当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - multiple rows into a single row

问题描述:

I trying to create a query in SQL SERVER 2008 that will display multiple row into a single row per account.

The table is currently displaying the data like so...(also the number of cds for each account is not the same)

table query

SELECT

[ACCOUNTNO]

,DT

,POSTDT

,CD

,NO

,SEQNO

FROM [dbo].[DInformation]

results

ACCOUNTNO DT POSTDT CD NO SEQNO

50000878915 06/29/2011 06/29/2011 52074 639 2005

50000878915 07/03/2008 07/03/2008 6650222 749 3008

50000878915 07/03/2008 07/09/2008 6720030 644 3032

50000878915 07/23/2008 07/23/2008 99801 995 2055

50000878915 07/23/2008 07/23/2008 99801 995 2056

50000878915 07/23/2008 07/23/2008 7109045 994 2134

50000878915 07/10/2008 07/10/2008 52017 996 169

50000878915 07/23/2008 07/23/2008 99801 995 2055

50000878915 07/23/2008 07/23/2008 99801 995 2056

50000878915 07/23/2008 07/23/2008 30010 992 2087

50000878915 07/23/2008 07/23/2008 52038 993 2032

65478078915 10/01/2012 01/09/2013 6910002 871 3076

65478078915 11/01/2012 01/09/2013 6910002 888 3055

65478078915 12/01/2012 01/09/2013 6910002 6 3085

65478078915 01/01/2013 02/06/2013 6910002 918 3009

65478078915 02/01/2013 03/05/2013 6910002 668 3018

65478078915 03/01/2013 04/04/2013 6910002 24 3065

I would like the results to display like so...

50000878915 06/29/2011 06/29/2011 52074 639 2005 07/03/2008 07/03/2008 6650222 749 3008 07/03/2008 07/09/2008 6720030 644 3032 07/23/2008 07/23/2008 99801 995 2055 07/23/2008 07/23/2008 99801 995 2056 07/23/2008 07/23/2008 7109045 994 2134 07/10/2008 07/10/2008 52017 996 169 07/23/2008 07/23/2008 99801 995 2055 07/23/2008 07/23/2008 99801 995 2056 07/23/2008 07/23/2008 30010 992 2087 07/23/2008 07/23/2008 52038 993 2032

65478078915 10/01/2012 01/09/2013 6910002 871 3076 11/01/2012 01/09/2013 6910002 888 3055 12/01/2012 01/09/2013 6910002 6 3085 01/01/2013 02/06/2013 6910002 918 3009 02/01/2013 03/05/2013 6910002 668 301803/01/2013 04/04/2013 6910002 24 3065

any help would be very appreciated

网友答案:

You could use XML functionality if you just want to list them out like your sample shows:

SELECT DISTINCT
       ACCOUNTNO
      ,STUFF((SELECT DISTINCT ',' + CONCAT(DT,POSTDT,CD,NO,SEQNO)
              FROM Table1 a 
              WHERE a.ACCOUNTNO = b.ACCOUNTNO
              FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

FROM Table1 b

Demo: SQL Fiddle

If you want to have them be proper fields then you're looking at some PIVOT action.

Edit: Note the CONCAT() function was added in 2012, if using prior version cast each field to string and concatenate via +.

分享给朋友:
您可能感兴趣的文章:
随机阅读: