Nov 8, 2013

How to select data two columns of a table as a single row in sql server

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