SQL server - convert rows into columns

Transact-SQL
1

Hello,
can anyone please help with this:
I need to convert rows into columns
This is the original data:
declare @Table AS TABLE
(
TYPE VARCHAR(100) ,
SERIE VARCHAR(100) ,
CUR1 INT,
CUR2 INT
)

INSERT @Table
( TYPE, SERIE, CUR1, CUR2)
VALUES
( 'CORP', 'S1' ,2122,322 ),
( 'CORP', 'S2' ,321,546 ),
( 'SER', 'S1',543,788 ),
( 'SER', 'S2' ,655, 988 )

This is the resulting table:

Captura
Captura.JPG789×156 14.5 KB

Many thanks in advanced,
pepe

2

Hi,

Here you go:

declare @Table AS TABLE
(
TYPE VARCHAR(100) ,
SERIE VARCHAR(100) ,
CUR1 INT,
CUR2 INT
)

INSERT @Table
( TYPE, SERIE, CUR1, CUR2)
VALUES
( 'CORP', 'S1' ,2122,322 ),
( 'CORP', 'S2' ,321,546 ),
( 'SER', 'S1',543,788 ),
( 'SER', 'S2' ,655, 988 )

SELECT SERIE, CUR1_CORP=Max(CUR1_CORP),CUR1_SER=Max([CUR1_SER]),CUR2_CORP=Max([CUR2_CORP]),CUR2_SER=Max([CUR2_SER])
FROM(
SELECT SERIE, CUR1_CORP=Max([CORP]),CUR1_SER=Max([SER]),CUR2_CORP=null,CUR2_SER=null
FROM @Table
PIVOT
(
SUM([Cur1])
FOR [Type] IN ([CORP],[SER])
) AS P
group by SERIE
union all
SELECT SERIE,CUR1_CORP=null,CUR1_SER=null, CUR2_CORP=Max([CORP]),CUR2_SER=Max([SER])
FROM @Table
PIVOT
(
SUM([Cur2])
FOR [Type] IN ([CORP],[SER])
) AS P
group by SERIE
)Qry
group by SERIE

Regards,
Micheale

1 Like
3

perfect - many thanks!

4

Another way to do this :-

declare @Table AS TABLE
 (
 TYPE VARCHAR(100) ,
 SERIE VARCHAR(100) ,
 CUR1 INT,
 CUR2 INT
 )

INSERT @Table
 ( TYPE, SERIE, CUR1, CUR2)
 VALUES
 ( 'CORP', 'S1' ,2122,322 ),
 ( 'CORP', 'S2' ,321,546 ),
 ( 'SER', 'S1',543,788 ),
 ( 'SER', 'S2' ,655, 988 )



select
 serie	
,cur1_corp	
,cur1_ser	
,cur2_corp	
,cur2_ser
from (
		 select 
		  [column2] +'_'+ [type] as to_pivot
		 ,serie
		 ,[column1]
		 from ( 
				 select * 
				 from @table unpivot ( 
				 [column1] for [column2] in([cur1],[cur2]))unpvt
		  )elma
)_ 
	pivot(max([column1]) for to_pivot in ( 
 [cur1_corp]
,[cur1_ser]	
,[cur2_corp]
,[cur2_ser]
))ptv
5

hi

please see article with different ways to make rows to columns

1 Like