question

ajay_7879 avatar image
ajay_7879 asked

SQL Query to get desired Output

Hi, My SQL table stores record for each date and time related to account. Data contains in this manner. ACCT NOS DT DSPID Rank 1243 123456 24-12-14 15:16:05 89 1.00 1243 234567 24-12-14 14:59:12 89 2.00 1243 123458 24-12-14 14:54:10 74 3.00 1243 234568 24-12-14 14:46:32 74 4.00 1243 645827 23-12-14 15:48:09 75 5.00 2341 123459 24-12-14 15:17:44 83 1.00 Want Output based on this format where ACCT will be group by and rest all result should come in adjacent column based on rank. For eg., in this manner ACCT NOS_RANK1 NOS_RANK2 NOS_RANK3 NOS_RANK4 NOS_RANK5 DSPID_RANK1 DSPID_RANK2 DSPID_RANK3 DSPID_RANK4 DSPID_RANK5 1243 123456 234567 123458 234568 645827 89 89 74 74 75 2341 123459 83 Please help me in getting the desired output.[link text][1] [1]: /storage/temp/ 1872-help.txt Also file is attached for references
sqlserver 2008
help.txt (576 B)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is this homework? Have you tried using PIVOT?
0 Likes 0 ·

1 Answer

·
zhll2046 avatar image
zhll2046 answered
to join two dynamic pivoted tables would help, please see below. --drop table testdb.dbo.tbl; create table testdb.dbo.tbl ( acct int, nos int, dt datetime, dspid int, [rank] int ); insert into tbl values (1243, 123456, '12-24-14 15:16:05', 89, 1) , (1243, 234567, '12-24-14 14:59:12', 89, 2) , (1243, 123458, '12-24-14 14:54:10', 74, 3) , (1243, 234568, '12-24-14 14:46:32', 74, 4) , (1243, 645827, '12-23-14 15:48:09', 75, 5) , (2341, 123459, '12-24-14 15:17:44', 83, 1) ; declare @nosColumns varchar(max); declare @dspColumns varchar(max); declare @nosPivotColumns varchar(max); declare @dspPivotColumns varchar(max); select @nosColumns=isnull(@nosColumns+',','')+quotename(ltrim(str([rank])))+' as nos_rank_'+ ltrim(str([rank])) from (select distinct [rank] from tbl) as t; select @nosPivotColumns=isnull(@nosPivotColumns+',','')+quotename(ltrim(str([rank])) )from (select distinct [rank] from tbl) as t; select @dspColumns=isnull(@dspColumns+',','')+quotename(ltrim(str([rank])))+' as dspId_rank_'+ ltrim(str([rank])) from (select distinct [rank] from tbl) as t; select @dspPivotColumns=@nosPivotColumns; declare @sqlStr nvarchar(max)=N' ;with cte as ( select acct , @nosColumns from (select acct,nos,[rank] from tbl) as a pivot ( max(nos) for [rank] in (@nosPivotColumns) )as t ), cte2 as ( select acct , @dspColumns from (select acct,dspid,[rank] from tbl) as a pivot ( max(dspId) for [rank] in (@dspPivotColumns) )as t ) select * from cte join cte2 on cte.acct=cte2.acct ' select @sqlStr=replace(replace(replace(replace(@sqlStr,'@nosColumns',@nosColumns),'@nosPivotColumns',@nosPivotColumns),'@dspColumns',@dspColumns),'@dspPivotColumns',@dspPivotColumns) EXEC sp_executesql @sqlStr drop table testdb.dbo.tbl
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.