CODEWITHSUNDEEP
sqlt-sqlsql-server-2005pivot-table
programmer
programmer

Reputation: 1094

Pivot or Transpose SQL Server 2005 table data without row-by-row processing

Is it possible to do the following in SQL Server 2005 without row-by-row processing? If so, how? :)

My table is like this:

╔════════════╦═══════════╦════════════╗
║ CustomerID ║ FirstName ║  LastName  ║
╠════════════╬═══════════╬════════════╣
║          1 ║ George    ║ Washington ║
║          2 ║ Benjamin  ║ Franklin   ║
║          3 ║ Thomas    ║ Jefferson  ║
╚════════════╩═══════════╩════════════╝

I to output the above table like this:

╔════════════╦══════════╦════════════╗
║   Field    ║ IntValue ║ TextValue  ║
╠════════════╬══════════╬════════════╣
║ CustomerID ║ 1        ║ NULL       ║
║ FirstName  ║ NULL     ║ George     ║
║ LastName   ║ NULL     ║ Washington ║
║ CustomerID ║ 2        ║ NULL       ║
║ FirstName  ║ NULL     ║ Benjamin   ║
║ LastName   ║ NULL     ║ Franklin   ║
║ CustomerID ║ 3        ║ NULL       ║
║ FirstName  ║ NULL     ║ Thomas     ║
║ LastName   ║ NULL     ║ Jefferson  ║
╚════════════╩══════════╩════════════╝

Thanks! Jason

Upvotes: 1

Views: 976

Answers (2)

Martin Smith
Martin Smith

Reputation: 453028

This only scans the table once (Borrowing the table variable from @Aaron's answer).

SELECT Field, 
       IntValue, 
       TextValue 
FROM   @x 
       CROSS APPLY (SELECT 'CustomerID', CustomerID,  NULL 
                    UNION ALL 
                    SELECT 'FirstName', NULL, FirstName
                    UNION ALL 
                    SELECT 'LastName', NULL, LastName) 
        CA(Field, IntValue, TextValue) 
ORDER BY CustomerID

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

While I still suggest this pivoting is much better performed at the layer where the SELECT is consumed, here is one idea.

DECLARE @x TABLE(CustomerID INT, FirstName VARCHAR(32), LastName NVARCHAR(32));

INSERT @x SELECT 1, 'George', 'Washington'
UNION ALL SELECT 2, 'Benjamin', 'Franklin'
UNION ALL SELECT 3, 'Thomas', 'Jefferson';

;WITH x AS
(
  SELECT 
    Field = 'CustomerID', IntValue = CustomerID, TextValue = NULL,
    rn = ROW_NUMBER() OVER (ORDER BY CustomerID)
    FROM @x
  UNION ALL
  SELECT 
    Field = 'FirstName', NULL, FirstName,
    rn = ROW_NUMBER() OVER (ORDER BY CustomerID)
    FROM @x
  UNION ALL
  SELECT
    Field = 'LastName', NULL, LastName,
    rn = ROW_NUMBER() OVER (ORDER BY CustomerID)
    FROM @x
)
SELECT Field, IntValue, TextValue
FROM x
ORDER BY rn, Field;

Why is this better done at the presentation later? Because this "solution" will scan the table three times. And the consumer is still going to have to use a loop to display the results...

Upvotes: 2

Related Questions