How to transform columns to rows dynamically

Transact-SQL
1

Hi

I have
--Table Structure & Result
select Field1='P001',Field2='A',Field3='A1',USD_Company__A=1000,USD_Company__B=null,USD_Company__C=766551,AUD_Company__A=null union all
select Field1='P001',Field2='B',Field3='A2',USD_Company__A=null,USD_Company__B=null,USD_Company__C=1111,AUD_Company__A=2222 union all
select Field1='P001',Field2='C',Field3='A3',USD_Company__A=1000,USD_Company__B=4000,USD_Company__C=1000,AUD_Company__A=9000

--How to Transform above Table Structure & Data to be as below resultset ???
select Field1='P001',Field2='A',Field3='A1',Currency='USD',Company='Company A',Amount=1000 union all
select Field1='P001',Field2='A',Field3='A1',Currency='USD',Company='Company B',Amount=null union all
select Field1='P001',Field2='A',Field3='A1',Currency='USD',Company='Company C',Amount=766551 union all
select Field1='P001',Field2='A',Field3='A1',Currency='AUD',Company='Company A',Amount=null union all
select Field1='P001',Field2='B',Field3='A2',Currency='USD',Company='Company A',Amount=null union all
select Field1='P001',Field2='B',Field3='A2',Currency='USD',Company='Company B',Amount=null union all
select Field1='P001',Field2='B',Field3='A2',Currency='USD',Company='Company C',Amount=1111 union all
select Field1='P001',Field2='B',Field3='A2',Currency='AUD',Company='Company A',Amount=2222 union all
select Field1='P001',Field2='c',Field3='A3',Currency='USD',Company='Company A',Amount=1000 union all
select Field1='P001',Field2='c',Field3='A3',Currency='USD',Company='Company B',Amount=4000 union all
select Field1='P001',Field2='c',Field3='A3',Currency='USD',Company='Company C',Amount=1000 union all
select Field1='P001',Field2='c',Field3='A3',Currency='AUD',Company='Company A',Amount=9000

The <><><<CompanyName (SPACE represent with __)>> is a dynamic list not static columns name.

Please advise.

Thanks

Regards,
Micheale

2 
SELECT ca1.*
FROM (
    select Field1='P001',Field2='A',Field3='A1',USD_Company__A=1000,USD_Company__B=null,USD_Company__C=766551,AUD_Company__A=null union all
    select Field1='P001',Field2='B',Field3='A2',USD_Company__A=null,USD_Company__B=null,USD_Company__C=1111,AUD_Company__A=2222 union all
    select Field1='P001',Field2='C',Field3='A3',USD_Company__A=1000,USD_Company__B=4000,USD_Company__C=1000,AUD_Company__A=9000
) AS data
CROSS APPLY (
   SELECT *
   FROM ( VALUES
       (data.Field1, data.Field2, data.Field3, 'USD', 'Company A', USD_Company__A), 
       (data.Field1, data.Field2, data.Field3, 'USD', 'Company B', USD_Company__B), 
       (data.Field1, data.Field2, data.Field3, 'USD', 'Company C', USD_Company__C), 
       (data.Field1, data.Field2, data.Field3, 'AUD', 'Company A', AUD_Company__A) 
    ) AS result(Field1, Field2, Field3, Currency, Company, Amount)
) AS ca1
ORDER BY Field1, Field2, Field3
3

Thanks Sir. I want the field to be dynamically change instead hardcoded. How can I do that for Currency & Company?

I only manage to write till this stage of my query:-
--Table Structure & Result
drop table tblTest
Create table tblTest (
Field1 nvarchar(max),
Field2 nvarchar(max),
Field3 nvarchar(max),
USD_Company__A nvarchar(max),
USD_Company__B nvarchar(max),
USD_Company__C nvarchar(max),
AUD_Company__A nvarchar(max)
)
insert into tblTest(Field1,Field2,Field3,USD_Company__A,USD_Company__B,USD_Company__C,AUD_Company__A)
select Field1='P001',Field2='A',Field3='A1',USD_Company__A=1000,USD_Company__B=null,USD_Company__C=766551,AUD_Company__A=null union all
select Field1='P001',Field2='B',Field3='A2',USD_Company__A=null,USD_Company__B=null,USD_Company__C=1111,AUD_Company__A=2222 union all
select Field1='P001',Field2='C',Field3='A3',USD_Company__A=1000,USD_Company__B=4000,USD_Company__C=1000,AUD_Company__A=9000

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'tblTest' and
                 C.column_name like '__%'
           for xml path('')), 1, 1, '')
select @colsUnpivot = Replace(@colsunpivot,'[Field1],[Field2],[Field3],','')

set @query 
  = 'select Field1,Field2,Field3,
	 indicatorvalue
     from tblTest
     unpivot
     (
        indicatorvalue
        for indicatorname in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

How can I add auto extract the currency & company name dynamically without hardcoded?

Thanks.

Regards,
Micheale

4

Hi,

Thanks a bunch.

I managed to get it right.
Here is the solution:-
--Table Structure & Result
drop table tblTest
Create table tblTest (
Field1 nvarchar(max),
Field2 nvarchar(max),
Field3 nvarchar(max),
USD_Company__A nvarchar(max),
USD_Company__B nvarchar(max),
USD_Company__C nvarchar(max),
AUD_Company__A nvarchar(max)
)
insert into tblTest(Field1,Field2,Field3,USD_Company__A,USD_Company__B,USD_Company__C,AUD_Company__A)
select Field1='P001',Field2='A',Field3='A1',USD_Company__A=1000,USD_Company__B=null,USD_Company__C=766551,AUD_Company__A=null union all
select Field1='P001',Field2='B',Field3='A2',USD_Company__A=null,USD_Company__B=null,USD_Company__C=1111,AUD_Company__A=2222 union all
select Field1='P001',Field2='C',Field3='A3',USD_Company__A=1000,USD_Company__B=4000,USD_Company__C=1000,AUD_Company__A=9000

DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @colsUnpivot
= stuff((select ','+quotename(C.column_name)
from information_schema.columns as C
where C.table_name = 'tblTest' and
C.column_name like '__%'
for xml path('')), 1, 1, '')
select @colsUnpivot = Replace(@colsunpivot,'[Field1],[Field2],[Field3],','')

set @query
= 'select Field1,Field2,Field3,
Currency=Left(indicatorname,3),
CompanyName=Replace(SUBSTRING(indicatorname, 5, len(indicatorname)),''_'','' ''),
indicatorvalue
from tblTest
unpivot
(
indicatorvalue
for indicatorname in ('+ @colsunpivot +')
) u'

exec sp_executesql @query;

Regards,
Micheale

1 Like
5

hi i had the same idea ..here is the link