Solved

MS SQL  Rows to Columns with unknown # columns

Posted on 2013-05-26
3
196 Views
Last Modified: 2013-05-28
I have a table that contains payment transactions for invoices.  Invoices can have an unknown # of payments.

ID   Invoice#     PaymentAmount    PaymentDate
========================================
1      102            100.45                     1/1/2012
2      102            95.45                       1/2/2012
3      102            50.45                       1/3/2012
4      103            259.00                     1/1/2012
5      104            24.00                       1/9/2012

I need to present the data similar to below:

Invoice#            Amount1     Date1           Amount2    Date2
(amount and date columns for each payment for the invoice)
==================================================
102                     100.45        1/1/2012      95.45          1/2/1/2012   50.45      1/3/2012
103                      259.00       1/1/2012
104                      24.00         1/9/2012

I can accomplish this if I have a set number of amount/payments but I seem to be struggling with having dynamic columns.
0
Comment
Question by:jmichael18
  • 2
3 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39197848
Hi Michael,

There are 3 approaches to this.  One is to join the tables together as many times as you need rows (you're doing this now).  Another is to write a UDF to take the rows and concatenate the data.  A third is to use recursive SQL.  There are a couple of articles on the DB2 forum that describe recursive SQL, and the examples are almost exactly what you're looking for.  The differences between DB2 SQL and Microsoft SQL are minor.

 http://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html

Take a look at that.  If it looks like something that you want to try, I'll be glad to help you with it.  It's really very flexible, and once you've done it a couple of times it's easy.


Kent
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39198233
this approach might work for you, in this example the dates are used as column headings like this
INVOICE  01/01/12   01/02/12 01/03/12   01/09/12
102      100        95       50         (null)
103      259       (null)   (null)      (null)
104      (null)    (null)   (null)      24

Open in new window

see: http://sqlfiddle.com/#!3/459d7e/1
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)


SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar, PaymentDate ,1) ) 
            FROM payments
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Invoice, ' + @cols + ' from 
            (
                select Invoice, PaymentAmount, PaymentDate
                from Payments
           ) x
            pivot 
            (
                 max(PaymentAmount)
                for PaymentDate in (' + @cols + ')
            ) p '

exec(@query)
;

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39198556
I've come up with a dynamic approach for this, but I want to stress that because it is dynamic it needs you to amend the code in 2 places to implement the "filter" of the data. It produces output in this fashion:
INVOICE AMOUNT1 DATE1    AMOUNT2  DATE2     AMOUNT3 DATE3
102      100    01/01/12   95     01/02/12  50      01/03/12
103      259    01/01/12  (null)  (null)   (null)   (null)
104      24    01/09/12   (null)  (null)   (null)   (null)

Open in new window

see it working at: http://sqlfiddle.com/#!3/807c6/2

Here is the code, and I have indicated where the "filter" should be included
DECLARE @query  AS NVARCHAR(MAX)

;WITH
COLS as (
          select
              ', max(CASE WHEN row_ref = ' 
            + convert(varchar,number) 
            + ' THEN PaymentAmount ELSE null END) AS Amount' 
            + convert(varchar,number)
            + ', max(CASE WHEN row_ref = ' 
            + convert(varchar,number) 
            + ' THEN convert(varchar, PaymentDate ,1) ELSE null END) AS Date' 
            + convert(varchar,number)
            as casea
          FROM master.dbo.spt_values
          WHERE  type = 'P'
          AND number BETWEEN 1 AND (
                                    SELECT max(c)
                                    FROM (
SELECT count(*) AS c
FROM payments
where 1=1 /* i.e. filter the data here */
GROUP BY invoice
                                        ) as x
                                    )
           )

select @query = 

+ 'WITH CTE as ('
+ '        select'
+ '            Invoice'
+ '          , PaymentAmount'
+ '          , PaymentDate'
+ '          , row_number() over (partition by invoice order by paymentDate) as row_ref'
+ '        from Payments'
+ '        where 1=1 /* i.e. filter the data here */'
+ '       )'
+ 'select invoice, ' 
+ STUFF( 
           ( SELECT casea
             FROM cols
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  
+ ' from CTE group by invoice'

exec(@query)
;

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now