CODEWITHSUNDEEP
sqlsql-server-2008pivot
Travis Stanley
Travis Stanley

Reputation: 53

How to display selected results on one row instead of multiple rows in SQL

I want to make my MS SQL 2008 select query display results in a very unique way that i hope somebody can help me accomplish.

Instead of having the results for 3 columns being displayed over 9 rows, i would like the data displayed in one row and have the 3 column names repeated 9 times.

I tried to Google this but i can't seem to find the right ideas anywhere.

Thanks a million to anyone that can help!

i wanted to post up pic's but i need rep points to do it which is so stupid! but any way i found a similar post that was answered for PostgreSQL 8.3. but i'm a total beginner so i don't understand how to translate is for MS SQL.

before:

col1 |  col2  |  col3   |
-----|--------|---------|
 a   |12/01/12| 13/01/12|
-----|--------|---------|
 b   |14/01/12| 14/01/12|

after:

col1 |  col2  |  col3   |col1 |  col2  |  col3   |
-----|--------|---------|-----|--------|---------|
 a   |12/01/12| 13/01/12| b   |14/01/12| 15/01/12|
-----|--------|---------|-----|--------|---------|

here's the link to give you a better idea of my situation Convert multiple rows into one row with multiple columns

Upvotes: 0

Views: 14753

Answers (1)

Taryn
Taryn

Reputation: 247810

In SQL Server 2005+ you can use the PIVOT function to perform this. A pivot takes your values from rows and converts it into columns.

There are a few ways that you can pivot the data. If you know all of the values ahead of time, then you can hard-code you query. Otherwise you could use dynamic SQL to generate the query at run-time.

A static version of a pivot would be similar to this:

select *
from
(
  select col1, col2
  from table1
) src
pivot
(
  max(col1)
  for col2 in (test, blah, value)
) piv

See SQL Fiddle with Demo.

If you have an unknown number of values, then you would generate dynamic SQL similar to this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(col2) 
                    from table1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
             (
                select col1, col2
                from table1
            ) x
            pivot 
            (
                max(col1)
                for col2 in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

These are taking the col2 row values from your table and turning them into columns. The result for both is the same:

| TEST | BLAH | VALUE |
-----------------------
|    1 |    2 |     3 |

Prior to SQL Server 2005 or in databases that do not have a pivot function, then you would use an aggregate function with a case expression to transform the data:

select 
  max(case when col2 = 'test' then col1 end) test,
  max(case when col2 = 'blah' then col1 end) blah,
  max(case when col2 = 'value' then col1 end) value
from table1

See SQL Fiddle with Demo

One another way to do this if you want to repeat the columns, over and over again is to use the UNPIVOT and PIVOT function together to get the result. If you have the sample data:

CREATE TABLE yourtable
    ([id] int, [name] varchar(10), [type] varchar(10))
;

INSERT INTO yourtable
    ([id], [name], [type])
VALUES
    (1, 'John', 'dog'),
    (2, 'Tim', 'bird'),
    (3, 'Betty', 'cat'),
    (4, 'Jim', 'rat')
;

And you want to repeat the id, name and type values in repeating columns, then you can use:

select *
from
(
  select 
    col +'_'+cast(rn as varchar(50)) col ,
    value
  from
  (
    select 
      cast(id as varchar(10)) id,
      name, 
      type,
      row_number() over(order by id) rn
    from yourtable
  ) src
  unpivot
  (
    value
    for col in (id, name, type)
  ) unpiv
) src
pivot
(
  max(value)
  for col in (id_1, name_1, type_1,
              id_2, name_2, type_2,
              id_3, name_3, type_3,
              id_4, name_4, type_4)
) piv

See SQL Fiddle with Demo. The result of this will be:

| ID_1 | NAME_1 | TYPE_1 | ID_2 | NAME_2 | TYPE_2 | ID_3 | NAME_3 | TYPE_3 | ID_4 | NAME_4 | TYPE_4 |
-----------------------------------------------------------------------------------------------------
|    1 |   John |    dog |    2 |    Tim |   bird |    3 |  Betty |    cat |    4 |    Jim |    rat |

Edit #2, seeing your sample data you could use the following:

select *
from
(
  select 
    col +'_'+cast(rn as varchar(50)) col ,
    value
  from
  (
    select 
      col1,
      convert(varchar(10), col2, 120) col2, 
      convert(varchar(10), col3, 120) col3, 
      row_number() over(order by col1) rn
    from yourtable
  ) src
  unpivot
  (
    value
    for col in (col1, col2, col3)
  ) unpiv
) src
pivot
(
  max(value)
  for col in (col1_1, col2_1, col3_1,
              col1_2, col2_2, col3_2)
) piv

See SQL Fiddle with Demo

Gives the result:

| COL1_1 |     COL2_1 |     COL3_1 | COL1_2 |     COL2_2 |     COL3_2 |
-----------------------------------------------------------------------
|      a | 2012-01-12 | 2012-01-13 |      b | 2012-01-14 | 2012-01-14 |

Upvotes: 3

Related Questions