CODEWITHSUNDEEP
sqlsql-serverpivotcoalesce
user1386006
user1386006

Reputation: 11

Display rows as columns

Here is my SQL table:

Table1
Col1   Col2
a      1
a      2
a      3
b      1
c      1
c      2

How can I query this table to display like this?

col1,value1,value2,value3
a,1,2,3
b,1,null,null
c,1,2,null

Each value in col1 will never have more than 5 records. I don't know what values are in col2, they will be dynamic.

Upvotes: 1

Views: 6117

Answers (2)

dan radu
dan radu

Reputation: 2782

If you are using SQL 2005 or later, PIVOT is a good option. For static queries, you can assume that the maximum number of values is 1, 2, 3... and use PIVOT like this:

SELECT Col1 AS 'Col1', 
[1], [2], [3], [4], [5]
FROM
(SELECT Col1, Col2 
    FROM dbo.Table1) AS SourceTable
PIVOT
(
MAX(Col2)
FOR Col2 IN ([1], [2], [3], [4], [5])
) AS PivotTable;

If you accept dynamic SQL, you can build the SQL statement and execute it using sp_executesql:

DECLARE @Values VARCHAR(1000)
DECLARE @SQL NVARCHAR(MAX)
SET @Values = NULL

SELECT 
    @Values = COALESCE(@Values + ', ', '') +  + '[' + CAST(Col2 AS VARCHAR) + ']'
FROM (SELECT DISTINCT TOP 100 PERCENT Col2 FROM dbo.Table1 ORDER BY 1) T

SET @SQL = '
SELECT Col1 AS ''Col1'', 
' + @Values + '
FROM
(SELECT Col1, Col2 
    FROM dbo.Table1) AS SourceTable
PIVOT
(
MAX(Col2)
FOR Col2 IN (' + @Values + ')
) AS PivotTable;
'
EXECUTE sp_executesql @SQL

Upvotes: 3

qwerty9967
qwerty9967

Reputation: 772

Databases are not very good at doing what you're asking. While I'm sure that it is possible and that someone will post the solution here, maybe you could make your life easier with a little bash script.

This code gets a list of all of the unique values in column 1. It then queries the table for all the values in column 2 for the given value in column 1. The "tr" command replaces the newline characters with commmas and the sed command switches the last comma back to a newline character.

I get the feeling that if you posted more about your particular problem, that there might be a better solution for your problem. Any solution that is a pure query is going to involve linking the table to itself 5 times, which could take a lot of processing depending on the size of your table.

Note: I have made a couple of assumptions since the question didn't have the details. I have assumed that we are using postgresql and that the database is called test.

#!/bin/bash                                                                     

echo "select distinct col1 from table1 order by col1" | psql -At test | while read col1;do
    echo -n "$col1,"
    echo "select col2 from table1 where col1='$col1' order by col2" | psql -At test | tr '\n' ',' | sed s/,$/\n/'

done

The output is then:

a,1,2,3
b,1
c,1,2

Upvotes: 0

Related Questions