Getting multiple rows from same table

Question

The problem I'm having is that there are more than one result in a table for what I'm trying to find. So if I have this:

SELECT DISTINCT student.ident AS [Ident],
     proghist.prgc AS [Test Code]
FROM student 
LEFT OUTER JOIN proghist

For some students I get multiple "Test Codes" so it'll look like this:

Ident   Test Code
123456   1
123456   4
654321   2
654321   6
122222   1

Is there a way to combine them to one row and separate columns?

Edit: I would like the data to be in the final result:

123456 1 4 
654321 2 6 
122222 1

What RDBMS are you using? What is the desired result of the query? Do you want the `TestCode` values comma separated or in multiple columns? — Taryn, Nov 26 '12 at 19:26
try to use pivot, http://stackoverflow.com/questions/24470/sql-server-pivot-examples — xurca, Nov 26 '12 at 19:34
I'm using MSSQL. I'm trying to get it to be something like: `123456 1 4` `654321 2 6` `122222 1` But on separate lines. Instead of what I have in the example above. — m0ngr31, Nov 26 '12 at 19:41

Taryn · Accepted Answer · 2012-11-26T19:52:50.317

Since you are using SQL Server, if you are using SQL Server 2005+, then you can use the PIVOT function. If you know you will only have up to two TestCodes per ident then you can hard-code the values:

select ident,
  [1] TestCode1,
  [2] TestCode2
from
(
  SELECT  s.ident AS Ident,
    p.prgc AS TestCode,
    row_number() over(partition by s.ident order by p.prgc) rn
  FROM student s
  LEFT OUTER JOIN proghist p
    on s.ident = p.ident
) src
pivot
(
  max(TestCode)
  for rn in ([1], [2])
) piv

See SQL Fiddle with Demo

If you have an unknown number of values of TestCodes, then you can use dynamic SQL to PIVOT the data:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(rn)
                    FROM
                    (
                      select cast(row_number() over(partition by s.ident order by p.prgc) as varchar(50)) rn
                      FROM student s
                      LEFT OUTER JOIN proghist p
                        on s.ident = p.ident
                    ) src
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(rn) + ' as TestCode'+rn
                    FROM
                    (
                      select cast(row_number() over(partition by s.ident order by p.prgc) as varchar(50)) rn
                      FROM student s
                      LEFT OUTER JOIN proghist p
                        on s.ident = p.ident
                    ) src
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Ident, ' + @colsPivot + ' from 
             (
               SELECT  s.ident AS Ident,
                p.prgc AS TestCode,
                row_number() over(partition by s.ident order by p.prgc) rn
              FROM student s
              LEFT OUTER JOIN proghist p
                on s.ident = p.ident
            ) src
            pivot 
            (
                max(TestCode)
                for rn in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

If you do not have access to the PIVOT function, then you can use an aggregate function with a CASE statement:

select ident,
  max(case when rn = 1 then testcode else '' end) TestCode1,
  max(case when rn = 2 then testcode else '' end) TestCode2
from
(
  SELECT  s.ident AS Ident,
    p.prgc AS TestCode,
    row_number() over(partition by s.ident order by p.prgc) rn
  FROM student s
  LEFT OUTER JOIN proghist p
    on s.ident = p.ident
) src
group by ident

See SQL Fiddle with Demo

All three will produce the same result:

|  IDENT | TESTCODE1 | TESTCODE2 |
----------------------------------
| 122222 |         1 |         0 |
| 123456 |         1 |         4 |
| 654321 |         2 |         6 |

Getting multiple rows from same table

1 Answers1