SQL-Server PIVOT/Transpose with dynamic column, convert row into column

If you have data like following and want to transpose row into column using sql-server 2008 PIVOT,

focomp	FOBUFMED1QT	FOBUFMED2QT
310	2	4
520	5	3
520	5	8
850	3	6
310	3	4
310	3	8
580	3	6

 

 

Transpose means you want Data like following in multiple column sum :

Like following:-

310	520	580	850
8	10	3	3
16	11	6	6

 

In this example my table name is BOFASY

Use following sql query to achive this:-

 

DECLARE @listCol VARCHAR(2000)

DECLARE @query VARCHAR(4000)

DECLARE @query1 VARCHAR(4000)

DECLARE @query2 VARCHAR(4000)

 

SELECT  @listCol = STUFF(( SELECT DISTINCT

                                '],[' + ltrim(focomp)

                        FROM    BOFASY

                        ORDER BY '],[' + ltrim(focomp)

                        FOR XML PATH('')

                                    ), 1, 2, '') + ']'

SET @query =

'SELECT * FROM

      (SELECT  focomp,cast(FOBUFMED1QT as int) FOBUFMED1QT FROM BOFASY) src

PIVOT (SUM(FOBUFMED1QT) FOR focomp

IN ('+@listCol+')) AS pvt'

 

SET @query1 =

'SELECT * FROM

      (SELECT  focomp,cast(FOBUFMED2QT as int) FOBUFMED2QT FROM BOFASY) src

PIVOT (SUM(FOBUFMED2QT) FOR focomp

IN ('+@listCol+')) AS pvt'

set @query2=@query +' union '+ @query1

 

EXECUTE (@query2)