1

I have a few items in the database in the form as below:

ID|ColName|ColValue
-------------------
1 |A      |1testa
1 |B      |1testb
1 |C      |1testc
1 |D      |1testd
2 |A      |2testa
2 |D      |2testd

I need data in the form below:

ID| A      | B      | C      | D
1 | 1testa | 1testb | 1testc | 1testd
2 | 2testa | NULL   |   NULL | 2testd

I have tried using PIVOT in T-SQL but it takes aggregate function as argument, which I do not want to supply.

How can I achieve this.

gotqn
  • 42,737
  • 46
  • 157
  • 243
Saksham
  • 9,037
  • 7
  • 45
  • 73

1 Answers1

2

The following code:

DECLARE @DataSource TABLE
(
     [ID] TINYINT
    ,[ColName] CHAR(1)
    ,[ColValue] VARCHAR(12)
);

INSERT INTO @DataSource ([ID], [ColName], [ColValue])
VALUES   (1, 'A', '1testa')
        ,(1, 'B', '1testb')
        ,(1, 'C', '1testc')
        ,(1, 'D', '1testd')
        ,(2, 'A', '2testa')
        ,(2, 'D', '2testd');

SELECT *
FROM @DataSource
PIVOT
(
    MAX([ColValue]) FOR [ColName] IN ([A], [B], [C], [D])
) PVT

is going to give you this:

enter image description here

Note, that when you are using the PIVOT/UNPIVOT clauses, you need to specified the columns (in your case A, B, C, D). If you do not want to hard-coded then, you need to build a dynamic pivot using - this can be done building the T-SQL statement in a string and executing it using sp_executesql.

gotqn
  • 42,737
  • 46
  • 157
  • 243