How do I convert a repeating column into a row?

Question

I have a table that has rows with unique values in one column. How can I place these in a row so that one entry has multiple entries?

For example here is a table...

create table visit_view
(
  last_name varchar(25),
  first_name varchar(25),
  middle_name varchar(25),
  dob datetime,
  di datetime,
  m varchar(20),
  d varchar(12),
  d_sequence_num smallint
)

insert into visit_view values ('LEE','BUDDY','','05/20/2010','10/01/2012','123456','786.2','2') 
insert into visit_view values   ('LEE','BUDDY','','05/20/2010','10/01/2012','123456','784.99','3') 
insert into visit_view values ('TU','BIBO','LU','09/29/2012','10/01/2012','321456','774.6','1') 
insert into visit_view values ('SMITH','BOBBIE','JOE','09/29/2012','10/01/2012','321654','V50.2','1')
insert into visit_view values ('LEWIS','CAREY','','11/11/2011','10/01/2012','654123','057.9','2')
insert into visit_view values ('LEWIS','CAREY','','11/11/2011','10/01/2012','654123','074.3','3')
insert into visit_view values ('RAMIREZ','HECTOR','','04/21/2011','10/02/2012','654321','381.81','2')
insert into visit_view values ('RAMIREZ','HECTOR','','04/21/2011','10/02/2012','654321','786.09','3')
insert into visit_view values ('RAMIREZ','HECTOR','','04/21/2011','10/02/2012','654321','380.4','4')
insert into visit_view values ('RAMIREZ','HECTOR','','04/21/2011','10/02/2012','654321','478.19','5')

And my query looks like this...

SELECT  
    first_name AS FirstName, 
    ISNULL(middle_name, '') AS MI, 
    last_name AS LastName, 
    CONVERT(varchar,dob, 101) DOB,
    CONVERT(varchar,di,101) DCdate,
    CAST(m AS INT) AS MR,
    d AS Diag
FROM 
    visit_view 
WHERE 
    d_sequence_num>1
    AND DATEDIFF(year,dob,GETDATE()) <= 3
ORDER BY di,d_sequence_num ASC

I my output is

FIRSTNAME MI LASTNAME DOB DCDATE MR DIAG 
CAREY  LEWIS 11/11/2011 10/01/2012 654123 057.9 
BUDDY  LEE 05/20/2010 10/01/2012 123456 786.2 
BUDDY  LEE 05/20/2010 10/01/2012 123456 784.99 
CAREY  LEWIS 11/11/2011 10/01/2012 654123 074.3 
HECTOR  RAMIREZ 04/21/2011 10/02/2012 654321 381.81 
HECTOR  RAMIREZ 04/21/2011 10/02/2012 654321 786.09 
HECTOR  RAMIREZ 04/21/2011 10/02/2012 654321 380.4 
HECTOR  RAMIREZ 04/21/2011 10/02/2012 654321 478.19

But I want it like this...

FIRSTNAME MI LASTNAME DOB DCDATE MR DIAG 
CAREY  LEWIS 11/11/2011 10/01/2012 654123 057.9 
BUDDY  LEE 05/20/2010 10/01/2012 123456 786.2 784.99
CAREY  LEWIS 11/11/2011 10/01/2012 654123 074.3 
HECTOR  RAMIREZ 04/21/2011 10/02/2012 654321 381.81 786.09 380.4 478.19

try: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Marc B, Jan 22 '13 at 19:12
And here for SQL Server solution: http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column — Art, Jan 22 '13 at 19:26

score 1 · Accepted Answer · answered Jan 22 '13 at 19:19

I am not sure why you have CAREY LEWIS showing twice in your result and HECTOR RAMIREZ once, but you can consolidate the rows using something like this:

SELECT  
    first_name AS FirstName, 
    ISNULL(middle_name, '') AS MI, 
    last_name AS LastName, 
    CONVERT(varchar,dob, 101) DOB,
    CONVERT(varchar,di,101) DCdate,
    CAST(m AS INT) AS MR,
    stuff((select distinct ', '+ d 
           from visit_view v1
           where v.first_name = v1.first_name
           FOR XML PATH('')),1,1,'') Diag   
FROM visit_view v
WHERE d_sequence_num>1
    AND DATEDIFF(year,dob,GETDATE()) <= 3
GROUP BY first_name, middle_name, last_name, dob, di, m
ORDER BY di ASC

See SQL Fiddle with Demo

Gives the result:

| FIRSTNAME | MI | LASTNAME |        DOB |     DCDATE |     MR |                           DIAG |
-------------------------------------------------------------------------------------------------
|     BUDDY |    |      LEE | 05/20/2010 | 10/01/2012 | 123456 |                  784.99, 786.2 |
|     CAREY |    |    LEWIS | 11/11/2011 | 10/01/2012 | 654123 |                   057.9, 074.3 |
|    HECTOR |    |  RAMIREZ | 04/21/2011 | 10/02/2012 | 654321 |  380.4, 381.81, 478.19, 786.09 |

Putting Carey twice was my mistake, this was exactly what I was looking for!! — dwtorres, Jan 22 '13 at 19:24
+1 for the SQL Fiddle - I didn't know that existed - it's been added to my bookmarks bar — Derek Tomes, Jan 22 '13 at 19:24
@dwtorres happy to help, be sure to accept one of the answers to your questions if they are helpful. You can accept by clicking the checkmark to the left of the answer — Taryn, Jan 22 '13 at 19:26

score 0 · Answer 2 · answered Jan 22 '13 at 19:19

I would suggest looking into CROSS APPLY and FOR XML:

SELECT  DISTINCT
    first_name AS FirstName, 
    ISNULL(middle_name, '') AS MI, 
    last_name AS LastName, 
    CONVERT(varchar,dob, 101) DOB,
    CONVERT(varchar,di,101) DCdate,
    CAST(m AS INT) AS MR,
     STUFF(D2.d, 1, 1, '')  AS Diag
FROM 
    visit_view v
CROSS APPLY ( SELECT ',' + d
              FROM visit_view v2
              WHERE v.last_name = v2.last_name
              ORDER BY d_sequence_num 
                  FOR XML PATH('') )  D2 ( d )
WHERE 
    d_sequence_num>1
    AND DATEDIFF(year,dob,GETDATE()) <= 3

Wasn't sure why you didn't have an ID in your visit_view table or would have used that in my cross apply where criteria, but this should get you going in the right direction.

Here is the SQL Fiddle.

Good luck.

How do I convert a repeating column into a row?

2 Answers2