Create a dynamic table based on existing table result in sql

Question

This is the actual table.

Month   RCount  Cond_Id Cond_desc
Jan-13  52  -1  N/A
Jan-13  194 0   NORMAL
Jan-13  86  2   ABNORMAL
Feb-13  54  -1  N/A
Feb-13  158 0   NORMAL
Feb-13  110 2   ABNORMAL
Mar-13  14  -1  N/A
Mar-13  113 0   NORMAL
Mar-13  90  2   ABNORMAL
Apr-13  3   -1  N/A
Apr-13  259 0   NORMAL
Apr-13  144 2   ABNORMAL
May-13  10  -1  N/A
May-13  693 0   NORMAL
May-13  305 2   ABNORMAL
May-13  1   4   CRITICAL
Jun-13  169 0   NORMAL
Jun-13  36  2   ABNORMAL
Jun-13  1   4   CRITICAL

I need the following results.

R_id    Cond_Id Cond_desc   Jan-13  Feb-13  Mar-13  Apr-13  May-13  Jun-13
1   -1  N/A         51  54  14  3   10  169
2   0   NORMAL          194 158 113 259 693 36
3   2   ABNORMAL    86  110 90  144 305 1
4   0   CRITICAL    0   0   0   0   1   0

Have you looked at `PIVOT`? [http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx] — PM 77-1, Aug 10 '13 at 15:24
@user4035 your result table is wrong, the value of `JUN-13` for `N/A` should be `NULL`. Is it correct? — Gaston Flores, Aug 10 '13 at 16:25
@GastonF. It's not me, I just edited the post. It's user2670643 — user4035, Aug 10 '13 at 17:52

score 0 · Answer 1 · answered Aug 10 '13 at 16:47

Assuming that you wont this:

Sum the values of rcount grouped by month.
The value of COND_ID in your example is wrong, for example for the case CRITICAL the value should be 4.
The field R_ID (rownumber) is only a count of the query.

The following query maybe will be useful:

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

select @cols = STUFF((SELECT ',' + QUOTENAME([MONTH]) 
                    from MYTABLE
                    group by [MONTH]
                    order by [MONTH]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ROW_NUMBER() over (order by COND_ID) AS R_ID,COND_ID,COND_DESC,' + @cols + ' 
            from 
            (
              SELECT 
                 COND_ID, 
                 COND_DESC,
                 rcount,
                 MONTH 
              FROM MYTABLE
            ) x
            pivot 
            (
                sum(rcount)
                for [MONTH] in (' + @cols + ')
            ) p '

execute sp_executesql @query;

Here you try this sql fiddle. This query I've created from the bluefeet's link.

Create a dynamic table based on existing table result in sql

1 Answers1