Using Pivot Query in SQL2005

Last Post 26 Aug 2006 05:06 PM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sugarBABY
New Member
New Member

--
18 Aug 2006 09:07 PM
hi

I have 2 crossTab query written in sql 2000 and am wondering how do i rewrite using pivot query in sql 2005.Thanks


---CrossTAB 1
Declare @sQry Varchar(8000)
Declare @Date int
Declare @LedgerType varchar(15)
Select @Date = 0,@sQry = 'Select * , '

While @Date < (Select Distinct Max(Period) From dbo.tblFinal where LedType = 'A')
Begin
Select @Date = Min(Period) From tblFinal Where Period > @Date and LedType = 'A'
Select @sQry = @sQry + ' Case When Period = ' + Convert(varchar,@Date) + ' and LedType = ''A''' + ' Then Amount Else 0 End As [' + 'A_' +Convert(varchar,@Date) + '],'
Select @sQry = @sQry + ' Case When Period = ' + Convert(varchar,@Date) + ' and LedType = ''A''' + ' Then Amount Else 0 End As [' + 'A_LM' +Convert(varchar,@Date) + '],'
End


Select @sQry = left(@sQry,len(@sQry)-1)



Select @sQry = @sQry + 'Into tblProcessDemo' + ' From tblFinal'

Exec(@sQry)

Go


---CorssTAB 2
DECLARE @SQLDynamic1 varchar(8000),
@SQLDynamic2 varchar(8000),
@SQLDynamic3 varchar(8000)

SELECT @SQLDynamic3 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN @SQLDynamic2 ELSE @SQLDynamic3 END,
@SQLDynamic2 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN @SQLDynamic1 ELSE @SQLDynamic2 END,
@SQLDynamic1 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN '' ELSE @SQLDynamic1 END,
@SQLDynamic1 = isnull(@SQLDynamic1 + ',' + char(13), '') + ' case when JRNAL_TYPE Like ''' + JRNAL_TYPE +
''' and Period = ' + cast(Period as varchar(10)) + ' and LedType = ''F''' + ' then Amount else 0 end as [' +
JRNAL_TYPE + '-' + cast(Period as varchar(15)) + ']'from tblProcessDemo where JRNAL_TYPE like 'FC%' group by JRNAL_TYPE, Period

exec('select *,' + @SQLDynamic3 + ' ' + @SQLDynamic2 + ' ' + @SQLDynamic1 + 'into tblProcessDemo1 from tblProcessDemo order by JRNAL_TYPE, Period')
SQLUSA
New Member
New Member

--
26 Aug 2006 05:06 PM
PIVOT is not going to help you with an already "pivoted" query.

You would have to get the results vertical, then you could apply PIVOT.

Look here for example: http://www.sqlusa.com/bestpractices2005/crosstabbyvendorbymonth/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices...orbymonth/

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/
The Best SQL Server 2005 Training in the World
SwePeso
New Member
New Member

--
07 Apr 2007 04:32 AM
How is the new SQL Server 2005 going to treat an unknown number of columns?
Not really a dynamic solution, right?

Here is one example of how to create a pivot/crosstab with a dynamic number of columns
http://www.sqlservercentral.com/col...server.asp
SQLUSA
New Member
New Member

--
07 Apr 2007 07:03 AM
Nice work Peter! Check out the link below.

Dynamic PIVOT/Crosstab example: http://www.sqlusa.com/bestpractices...amicpivot/ .

Kalman Toth, Data Warehouse/OLAP Architect
SQL Server 2005 Training - http://www.sqlusa.com/datawarehouse
SwePeso
New Member
New Member

--
07 Apr 2007 12:29 PM
Does not work with column names that has a ] in it.
Use the built-in QUOTENAME function.
SwePeso
New Member
New Member

--
08 Apr 2007 05:14 AM
quote:

Originally posted by: SQLUSA
Dynamic PIVOT/Crosstab example: [L=http://www.sqlusa.com/bestpractices2005/dynamicpivot/]

Not so nice work to invite for SQL INJECTION...

You are not authorized to post a reply.

Acceptable Use Policy