CODEWITHSUNDEEP
sqlsql-server-2008t-sqlpivot
Naveen
Naveen

Reputation: 1502

Convert a row as column and merge two column as its value

I have stuck in a select statement, converting rows into columns. I have tried with PIVOT, i was able to convert the single column. But my requirement is little different. I have explained the requirement below.

I have a table structure as below,

enter image description here

I want to select the data as below,

enter image description here

The values in the table are dynamic, which is not a problem for me to deal with that. But i need a way to get the below result.

Could someone please give me a hint on doing it, may be a way to modify the PIVOT below.

 select *  
 from   
 (  
   select TSID,AID,Count,BID
   from tbl TS
   WHERE TS.TPID = 1  
 ) src  
 pivot  
 (  
   sum(Count)
   for AID in (AID1,AID2,AID3)  
 ) piv

Thank you..

Upvotes: 0

Views: 172

Answers (1)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

You may check this fiddle

EDIT This will work for not previously known column names

DECLARE @Columns AS VARCHAR(MAX)
DECLARE @SQL AS VARCHAR(MAX)
SELECT @Columns = STUFF(( SELECT DISTINCT ',' + AID
                  FROM Table1 
                FOR
                  XML PATH('')
                ), 1, 1, '')
SET @SQL = '
;WITH MyCTE AS
(
  SELECT  TSID,
          AID,
          STUFF(( SELECT '','' + CONVERT(VARCHAR,[Count] )
                  FROM Table1 I Where I.TSID = O.TSID
                FOR
                  XML PATH('''')
                ), 1, 1, '''') AS CountList
  FROM    Table1 O
  GROUP BY TSID,
          AID
 )

SELECT *
  FROM  MyCTE
PIVOT
(
  MAX(CountList)
  FOR AID IN 
  (
    ' + @Columns + '
  )
) AS PivotTable'



EXEC(@SQL)

Upvotes: 1

Related Questions