Create a one row table with as many columns as there are rows in a table returned from a SELECT query.

Question

I want a query to create a one row table with as many columns as there are rows in a table returned from a SELECT query., where the columns created have names which are values taken from some column of the SELECT query, and have values which are values taken from some other column of the SELECT query.

e.g.

If I have a table T1 with two columns as follows :

Field  Value
  A       1
  B       2
  C       3
  D       4

Then I want a query that will return the following one row table T2 as result :

COLUMN NAMES :    A       B       C       D
COLUMN VALUES:    1       2       3       4

Answer 1

This is not a very polished solution, but it will give you an idea as to how you can achieve this using dynamic SQL.

--** Set up a table to test this with
DECLARE @TestTable TABLE (Field VARCHAR(20), VALUE INT);

INSERT INTO @TestTable (Field, VALUE)
SELECT 'A',1 UNION
SELECT 'B',2 UNION
SELECT 'C',3 UNION
SELECT 'D',4


--** Set up variables
DECLARE @ColCount INT;
DECLARE @Loop INT;
DECLARE @ColName VARCHAR(20);
DECLARE @Value INT;
DECLARE @SQL VARCHAR(255);

--** Create temp table to hold the results and insert the row for populating
CREATE TABLE #ResultSet (dummy INT);
INSERT INTO #ResultSet  (dummy) VALUES (0);  

--** Get count of required columns
SELECT @ColCount = COUNT(*)
FROM @TestTable AS tt

SET @Loop = 1

WHILE @Loop <= @ColCount BEGIN
    --** Get column name and value
    WITH Cols AS
    (
        SELECT tt.Field
             , tt.VALUE
             , ROW_NUMBER() OVER (ORDER BY tt.Field) AS row
        FROM   @TestTable AS tt
    )
    SELECT @ColName = Field
         , @Value = Value
    FROM   Cols
    WHERE  row = @Loop;

    --** Add the column
    SET @SQL = 'ALTER TABLE #ResultSet ADD ' + @ColName + ' INT;'; 
    EXEC(@SQL);

    --** Insert the value   
    SET @SQL = 'UPDATE #ResultSet SET ' + @ColName + ' = ' + CONVERT(VARCHAR(50),@Value) 
    EXEC(@SQL);

    SET @Loop = @Loop + 1;
END

--** Drop the dummy column
ALTER TABLE #ResultSet DROP COLUMN [dummy];

SELECT *
FROM #ResultSet;

DROP TABLE #ResultSet;

Answer 2

A simple query to solve this:

SELECT  SUM(CASE C1 WHEN 'A' THEN C2 ELSE 0 END) AS 'A',
        SUM(CASE C1 WHEN 'B' THEN C2 ELSE 0 END) AS 'B',
        SUM(CASE C1 WHEN 'C' THEN C2 ELSE 0 END) AS 'C',
        SUM(CASE C1 WHEN 'D' THEN C2 ELSE 0 END) AS 'D'
FROM T1

An organic query:

DECLARE @C1 CHAR(1), @SQL VARCHAR(500)
DECLARE array CURSOR
    FOR SELECT C1
        FROM T1
        ORDER BY C1

OPEN array

FETCH NEXT FROM array
INTO @C1;
SET @SQL = 'SELECT  SUM(CASE C1 WHEN ''' + @C1 + ''' THEN C2 ELSE 0 END) AS ''' + @C1 + ''''
FETCH NEXT FROM array
INTO @C1;
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @SQL = @SQL + ', SUM(CASE C1 WHEN ''' + @C1 + ''' THEN C2 ELSE 0 END) AS ''' + @C1 + ''''
        FETCH NEXT FROM array
        INTO @C1
    END
SET @SQL = @SQL + 'FROM T1'

CLOSE array
DEALLOCATE array

EXEC(@SQL)