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
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
16 People are following this question.
