Suppose we have KeyValue like this:
Id
|
Key
|
Value
|
1
|
Name
|
Scott
|
2
|
Age
|
42
|
3
|
DOJ
|
12/05/2011
|
4
|
IsActive
|
1
|
We want to write a sql query which returns data of Key column as table header and data of Value column as row like following table:
Name
|
Age
|
DOJ
|
IsActive
|
Scott
|
42
|
12/05/2011
|
1
|
Solution 1:
DECLARE @SqlQuery AS VARCHAR(MAX) = 'SELECT '
SELECT @SqlQuery = @SqlQuery + '''' + Value + ''' AS ' +[KEY] + ', ' FROM KeyValue
SET @SqlQuery = LEFT(@SqlQuery,LEN(@SqlQuery)-1)
EXECUTE(@SqlQuery)
Solution 2:
This solution is only useful if total number of rows in the table is fixed
SELECT
(SELECT Value FROM KeyValue WHERE ID = 1) AS Name ,
(SELECT Value FROM KeyValue WHERE ID = 2) AS Age,
(SELECT Value FROM KeyValue WHERE ID = 3) AS DOJ,
(SELECT Value FROM KeyValue WHERE ID = 4) AS IsActive
No comments:
Post a Comment