CODEWITHSUNDEEP
sqlsql-serverpivot
akhrot
akhrot

Reputation: 445

Pivot For Dynamic Column Value

May Some One Please help me how to get the below output. SQL SERVER 2008

CREATE TABLE #TABLE
(
[Generic Name] varchar(15),
[Description]varchar(15)
)

INSERT #TABLE

SELECT 'RESISTOR',  'POWER' UNION ALL
SELECT 'RESISTOR',  'Type' UNION ALL
SELECT 'RESISTOR',  'DESIGN' UNION ALL
SELECT 'RESISTOR',  'Material'

result for each Generic Name , Design, Material,other, Power and TYPE as Value of the column under Column Name as Desc1, Desc2....DescN

**Generic Name    DESC1   ,DESC2  ,DESC2, DESC4   
RESISTOR        POWER   , Type    DESIGN, Material**

Upvotes: 0

Views: 56

Answers (2)

Ilyes
Ilyes

Reputation: 14928

Here is how to do it dynamicly:

CREATE TABLE T1
(
[Generic Name] varchar(15),
[Description]varchar(15)
)

INSERT T1

SELECT 'RESISTOR',  'POWER' UNION ALL
SELECT 'RESISTOR',  'Type' UNION ALL
SELECT 'RESISTOR',  'DESIGN' UNION ALL
SELECT 'RESISTOR',  'Material';



DECLARE @Cols NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Sel NVARCHAR(MAX);

SET @Cols = STUFF((SELECT distinct ',' + QUOTENAME(T1.Description) 
            FROM T1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');
SET @Sel = STUFF((SELECT distinct ',' + QUOTENAME(T1.Description) + ' AS DESC'+CAST(row_number () over (order by (Description)) as varchar(10))
            FROM T1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,''); 



SET @SQL = N'
SELECT [Generic Name], '+ @Sel+ N'
FROM T1
    pivot
    (
           MAX(Description) For Description IN ('+ @Cols + N')
        ) P';

EXECUTE( @SQL);

Results:

+--------------+--------+----------+-------+-------+
| Generic Name | DESC1  |  DESC2   | DESC3 | DESC4 |
+--------------+--------+----------+-------+-------+
| RESISTOR     | DESIGN | Material | POWER | Type  |
+--------------+--------+----------+-------+-------+

Demo

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Try the following Answer, Hope it helps you:

CREATE TABLE #TABLE
(
[Generic Name] varchar(15),
[Description]varchar(15)
)

INSERT INTO #TABLE

SELECT 'RESISTOR',  'POWER' UNION ALL
SELECT 'RESISTOR',  'Type' UNION ALL
SELECT 'RESISTOR',  'DESIGN' UNION ALL
SELECT 'RESISTOR',  'Material'

SELECT MAX(CASE WHEN RN=1 THEN [Generic Name] END)[Generic Name]
    ,MAX(CASE WHEN RN=1 THEN [Description] END)DESC1
    ,MAX(CASE WHEN RN=2 THEN [Description] END)DESC2
    ,MAX(CASE WHEN RN=3 THEN [Description] END)DESC3
    ,MAX(CASE WHEN RN=4 THEN [Description] END)DESC4
FROM(
    SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))RN from #TABLE
    )D

DROP TABLE #TABLE

Result:

Generic Name    DESC1   DESC2   DESC3   DESC4
RESISTOR        POWER   Type    DESIGN  Material

Upvotes: 0

Related Questions