CODEWITHSUNDEEP
sqlsql-servert-sql
Triumph Spitfire
Triumph Spitfire

Reputation: 645

Columns to Rows without UNPIVOT

DECLARE @temp TABLE(Client_ID INT, Data_DT VARCHAR(20), Amt_1 INT, Amt_2 INT, Amt_3 INT, Amt_4 INT, Amt_5 INT)

INSERT INTO @tempVALUES (1, '082014', 100, 0, 200, 300, 0)

I need the output like this:

Client_ID   Date_DT Amount_Col  Amount_Val
1           082014  Amt_1      100
1           082014  Amt_2      0
1           082014  Amt_3      200
1           082014  Amt_4      300
1           082014  Amt_5      0

Can this be done without using UNPIVOT?

Upvotes: 0

Views: 3750

Answers (4)

SubqueryCrunch
SubqueryCrunch

Reputation: 1495

Managed to get so far. 

IF OBJECT_ID('temp') IS NOT NULL DROP TABLE temp
GO

CREATE TABLE temp (Client_ID INT, Data_DT VARCHAR(20), Amt_1 INT, Amt_2 INT, Amt_3 INT, Amt_4 INT, Amt_5 INT)

INSERT INTO temp VALUES (1, '082014', 100, 0, 200, 300, 0)

DECLARE 
    @Query NVARCHAR(500),
    @Columns NVARCHAR(1000)

SET @Columns = ''

SELECT @Columns = @Columns + '('+name+'),'
FROM sys.columns 
WHERE object_id = OBJECT_ID('temp')
AND name NOT IN ('Client_ID','Data_DT')

SET @Columns = LEFT(@Columns,LEN(@Columns)-1)
PRINT @Columns

SET @Query = 
'
SELECT a.Client_ID, a.Data_DT, b.* FROM temp as a
CROSS APPLY (VALUES '+@Columns+')b (Amt)
'

EXEC(@Query)

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 1541

can you please try with this, the only difference is i created permanent table to get column details from 'INFORMATION_SCHEMA.COLUMNS '

Create Table using below script

Create TABLE temp (Client_ID INT, Data_DT VARCHAR(20), Amt_1 INT, Amt_2 INT, Amt_3 INT, Amt_4 INT, Amt_5 INT)
INSERT INTO temp VALUES(1, '082014', 100, 0, 200, 300, 0)
INSERT INTO temp VALUES(2, '082015', 200, 1, 300, 400, 1)

Select * From temp

-- Unpivot exclude column COLUMN_NAME<>'Client_ID' from unpivot just to set pkey for a record set.. change as per your requirement 

SELECT * FROM
(Select t.Client_ID,t.Data_DT,COLUMN_NAME,
     Case
     When COLUMN_NAME='Amt_1' Then t.Amt_1
     When COLUMN_NAME='Amt_2' Then t.Amt_2
     When COLUMN_NAME='Amt_3' Then t.Amt_3
     When COLUMN_NAME='Amt_4' Then t.Amt_4
     When COLUMN_NAME='Amt_5' Then t.Amt_5
     --When COLUMN_NAME='Data_DT' Then t.Data_DT
     End AS [Value]
     From INFORMATION_SCHEMA.COLUMNS C
     CROSS JOIN temp t
     Where TABLE_NAME ='temp' AND C.COLUMN_NAME<>'Client_ID' AND C.COLUMN_NAME<>'Data_DT'

) Result Where Value > 0 Order By Client_ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Of course. Th easiest way is simply union all:

select client_id, date_dt, 'amt_1' as amount_col, amt_1 as amount from @temp union all
select client_id, date_dt, 'amt_2' as amount_col, amt_2 from @temp union all
select client_id, date_dt, 'amt_3' as amount_col, amt_3 from @temp union all
select client_id, date_dt, 'amt_4' as amount_col, amt_4 from @temp union all
select client_id, date_dt, 'amt_5' as amount_col, amt_5 from @temp;

Another method is to use cross apply:

select t.client_id, t.date_dt, v.amount_col, v.amount
from @temp t cross apply
     (values ('amt_1', amt_1),
             ('amt_2', amt_2),
             ('amt_3', amt_3),
             ('amt_4', amt_4),
             ('amt_5', amt_5)
     ) v(amount_col, amount);

The easiest way to do amounts that are greater than 0 is:

select client_id, date_dt, 'amt_1' as amount_col, amt_1 as amount from @temp where amt_1 > 0 union all
select client_id, date_dt, 'amt_2' as amount_col, amt_2 from @temp where amt_2 > 0 union all
select client_id, date_dt, 'amt_3' as amount_col, amt_3 from @temp where amt_3 > 0 union all
select client_id, date_dt, 'amt_4' as amount_col, amt_4 from @temp where amt_4 > 0 union all
select client_id, date_dt, 'amt_5' as amount_col, amt_5 from @temp where amt_5 > 0;

Upvotes: 3

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use UNION ALL

SELECT
    Client_ID,
    Data_DT,
    Amount_Col  = 'Amt_1',
    Amount_Val = Amt_1
FROM @temp
UNION ALL
SELECT
    Client_ID,
    Data_DT,
    Amount_Col  = 'Amt_2',
    Amount_Val = Amt_2
FROM @temp
UNION ALL
SELECT
    Client_ID,
    Data_DT,
    Amount_Col  = 'Amt_3',
    Amount_Val = Amt_3
FROM @temp
UNION ALL
SELECT
    Client_ID,
    Data_DT,
    Amount_Col  = 'Amt_4',
    Amount_Val = Amt_4
FROM @temp
UNION ALL
SELECT
    Client_ID,
    Data_DT,
    Amount_Col  = 'Amt_5',
    Amount_Val = Amt_5
FROM @temp
ORDER BY
    Client_ID,
    Data_DT,
    Amount_Col

Upvotes: 1

Related Questions