CODEWITHSUNDEEP
sqlsql-serversql-server-2008
AB Vyas
AB Vyas

Reputation: 2389

Pivot Header Data in row using sql server

Is there any way to make table pivot in sql server like such a way.

I have data like 

| OldItem | NewItem |
---------------------
|   HD1   |   365   |

I need output like below.

|    Name     | Value1 |
---------------------
|   OldItem   |   HD1  |
|   NewItem   |   365  |

Thanks in advance.

Upvotes: 0

Views: 103

Answers (3)

Darka
Darka

Reputation: 2768

And here is my little code :D

DECLARE @dataTable TABLE (OldItem VARCHAR(10), NewItem INT)

INSERT INTO @dataTable SELECT 'HD1', 365
INSERT INTO @dataTable SELECT 'HD2', 300
INSERT INTO @dataTable SELECT 'HD3', 200
INSERT INTO @dataTable SELECT 'HD4', 200


--first select data what you need and add upcoming new column name 
SELECT 'Value' + CAST(ROW_NUMBER() OVER (ORDER BY OldITem) AS VARCHAR) AS NewColumn, 'OldItem' as RowName, OldItem AS Item
INTO #SelectedData
FROM @dataTable
WHERE OldItem IN ('HD1', 'HD2', 'HD3')
UNION ALL
SELECT 'Value' + CAST(ROW_NUMBER() OVER (ORDER BY OldITem) AS VARCHAR) AS NewColumn, 'NewItem' as RowName, CAST(NewItem AS VARCHAR) AS Item
FROM @dataTable
WHERE OldItem IN ('HD1', 'HD2', 'HD3')


--Collect what column names will be
DECLARE @columns NVARCHAR(MAX) = (
                                SELECT STUFF(
                                    (SELECT DISTINCT ', [' + NewColumn + ']'
                                    FROM #SelectedData
                                    FOR XML PATH ('')),
                                    1, 2, '' )
                                )




-- create dynamic code for pivot

DECLARE @dynamicSQL AS NVARCHAR(MAX);

SET @dynamicSQL = N'
SELECT RowName, ' + @columns + '
FROM #SelectedData
PIVOT (MIN(Item) FOR NewColumn IN (' + @columns + ')) AS T
';

EXEC sp_executesql @dynamicSQL

Upvotes: 0

TechDo
TechDo

Reputation: 18659

Please try using UNPIVOT. Sample given is for static two rows.

SELECT Name, Value1
FROM 
   (SELECT *
   FROM tbl) p
UNPIVOT
   (Value1 FOR Name IN 
      (OldItem, NewItem)
)AS unpvt;

Upvotes: 2

BarneyL
BarneyL

Reputation: 1362

The following works for me:

Create Table #Values (OldItem char(3),NewItem int);

INSERT INTO #Values (OldItem, NewItem)
VALUES ('HD1',365)
        ,('HD2',300)
        ,('HD3',200);

With Values_Ordered AS
(
SELECT OldItem, NewItem, row_number() OVER (ORDER BY OldItem) AS Sequence
FROM #Values
)
SELECT 'OldItem' AS Name,
    min(CASE WHEN Sequence = 1 THEN OldItem ELSE NULL END) AS Value1,
    min(CASE WHEN Sequence = 2 THEN OldItem ELSE NULL END) AS Value2,
    min(CASE WHEN Sequence = 3 THEN OldItem ELSE NULL END) AS Value3
FROM Values_Ordered
UNION ALL
SELECT 'NewItem' AS Name,
    min(CASE WHEN Sequence = 1 THEN CAST(NewItem AS CHAR(3)) ELSE NULL END) AS Value1,
    min(CASE WHEN Sequence = 2 THEN CAST(NewItem AS CHAR(3)) ELSE NULL END) AS Value2,
    min(CASE WHEN Sequence = 3 THEN CAST(NewItem AS CHAR(3)) ELSE NULL END) AS Value3
FROM Values_Ordered

Upvotes: 0

Related Questions