Transpose rows to Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transpose rows to Column

Author

Topic

thiyait
Yak Posting Veteran

70 Posts

Posted - 2014-06-03 : 01:52:20

Hi i have a query which is giving result like this

Moth POamount INvoiceamount PAckslipamount etc...
1 12334 444325 345324
2 43433 435 3452
3 34333 353 3434
4 1234 NULL NULL

Want transpose into like this

header 1 2 3 4
POamount 12334 43433 34333 1234
INvoiceamount 444325 435 353 NULL
PAckslipamount 345324 3452 3434 NULL
etc..

Please help how to write query using pivot\unpivot for this or any pointers.

Thanks in advance
thiya

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-03 : 02:52:33

[code]
;with aCTE
AS
(
select 1 as CustID,1 as [Month],12334 as POamount,444325 as INvoiceamount,345324 as PAckslipamount Union all
select 1,2, 43433, 435, 3452 union all
select 1,3, 34333, 353, 3434 union all
select 1,4, 1234, NULL, NULL
)
,
PoCTE
AS
(select 'PoAmount' as header, [month],PoAmount FROM aCTE)
,InCTE
AS
(select 'INvoiceamount' as header, [month],INvoiceamount FROM aCTE)
,PACTE
AS
(select 'PAckslipamount' as header, [month],PAckslipamount FROM aCTE)

select *
from PoCTE
Pivot( SUM(POamount) FOR [Month] IN ([1],[2],[3],[4]))A
UNION ALL
select *
from InCTE
Pivot( SUM(INvoiceamount) FOR [Month] IN ([1],[2],[3],[4]))A
UNION ALL
select *
from PaCTE
Pivot( SUM(PAckslipamount) FOR [Month] IN ([1],[2],[3],[4]))A
[/code]

output
[code]
header 1 2 3 4
PoAmount 12334 43433 34333 1234
INvoiceamount 444325 435 353 NULL
PAckslipamount 345324 3452 3434 NULL
[/code]

sabinWeb MCP

adsingh82
Starting Member

20 Posts

Posted - 2014-06-04 : 01:10:14

you can find how to write pivot query using this below url

[url]http://dotnetbites.com/example-pivot-unpivot-sql-server[/url]

Regards,
Alwyn.M

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-06 : 05:33:33

What you need to use is UNPIVOT and then pivot the result set..
Try this


CREATE TABLE DynamicPvt([Month] int,POamount int,INvoiceamount int,PAckslipamount int)
INSERT INTO DynamicPvt VALUES(1 ,12334 ,444325 ,345324)
,(2 ,43433 ,435 ,3452)
,(3 ,34333 ,353 ,3434)
,(4 ,1234 ,NULL ,NULL)

SELECT * FROM
(	SELECT Header,[MONTH],Value
	FROM (	SELECT	         CAST([MONTH]          AS varchar(50)) AS [MONTH]
				,CAST([POamount]       AS varchar(50)) AS [POamount]
			        ,CAST([INvoiceamount]  AS varchar(50)) AS [INvoiceamount]
			        ,CAST([PAckslipamount] AS varchar(50)) AS [PAckslipamount] FROM DynamicPvt
		  ) s
	UNPIVOT (Value FOR Header IN ([POamount],[INvoiceamount],[PAckslipamount]))u
)m
PIVOT(MAX(Value) FOR [MONTH] IN ([1],[2],[3],[4]))p

Notice that you won't get correct order as you expected ..

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources