Using SQL to generate columns that contain sparse data?

Question

I was wondering if you can do the following with SQL. Suppose you have a table like this:

r| Company | SoldToCustomer | Amount  |  Date
1|    A    |       A1       |  100    | 27-12-1989
2|    A    |       A2       |  200    | 20-02-1990
3|    A    |       A1       |  500    | 20-03-1991
4|    A    |       A2       |  800    | 30-01-1992
5|    B    |       A1       |  900    | 31-01-1992
6|    B    |       A2       |  1000   | 01-02-1993
...

Notice: that the r is just an indicator and not part of the table. It indicates the row number. I would like to transform it into a table like the following:

r| Company | AmountToCompanyA1  | AmountToCompanyA2 |  Date
1|   A     |      100           |         .         | 27-12-1989
2|   A     |       .            |        200        | 20-02-1990
3|   A     |      500           |         .         | 20-03-1991
4|   A     |       .            |        800        | 30-01-1992
5|   B     |      900           |         .         | 31-01-1992
...

Where . indicates a missing value. I am well aware that this kind of storage is redundantly and it is giving me a very big table, but I was wondering whether this kind of transformation is possible in SQL.

Please be aware that even though I have only used two A1 and A2, there are about 9000 A's, so this is impossible to do by hand.

I actually only wanted to create a SQL view of this.

PS: I am trying to do this in Microsoft SQL Server Management Studio.

Thanks!

You can use dynamic pivot: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Giorgos Betsos, Mar 13 '15 at 07:01
If I understand well, you will have a table with 9000 columns (all the A's) ? You can do something to automate the filling of these columns, which makes 2 or 50 columns the same (no need to manual fill), but 9000 is crazy. See https://msdn.microsoft.com/en-us/library/ms143432.aspx#Engine for SQL Server limitations. Rather than use a plain table or even a view, also try PIVOT. This operator is intended for this form of data (but volume will probably remain an issue). — AFract, Mar 13 '15 at 07:07
Why dont you use Case ? select (Case SoldToCustomer when 'A1' then Amount else null End) AmountToCompanyA1 , (Case SoldToCustomer when 'A2' then Amount else null End) AmountToCompanyA2 from ....... — newuserua_ext, Mar 13 '15 at 07:08
I am not going to use case, because in that case I would have to either type those statements manually or program something to generate those statements. Then again 9000 is indeed a bit too much I suppose and I probably should just do this in R or something. — Snowflake, Mar 13 '15 at 07:21

score 1 · Accepted Answer · answered Mar 13 '15 at 07:46

You need to use dynamic PIVOT as below

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.SoldToCustomer) 
            FROM table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT company, ' + @cols + ',date from 
            (
                select company, date
                    , amount
                    , SoldToCustomer
                from table1
           ) x
            pivot 
            (
                 max(amount)
                for SoldToCustomer in (' + @cols + ')
            ) p '


execute(@query)

Using SQL to generate columns that contain sparse data?

1 Answers1