How to design Query for creating dynamic columns from rows

Question

I have data

Table1

ID     Name
-----------
1      n1
2      n2
3      n4

Table2

FID   YearS    Val
----------------------
1     2008     Up
1     2009     Down
1     2010     Up
2     2000     Up
2     2001     Down
2     2002     Up
2     2003     Up
3     2009     Down
3     2010     Up

I want to return data in following format:

ID  Yr1  Val1    Yr2   Val2  Yr3   Val3    Yr4  Val4
--------------------------------------------------------
1   2008 Up      2009  Down  2010  Up      NULL Null
2   2000 Up      2001  Down  2002  Up      2003 Up
3   2009 Down    2010  Up    NULL  NULL    NULL Null

Based on maximum no of columns for ID i want to create column names and then convert rows in columns. Is this possible to do using a sql query?

Answer 1

This query should help

;WITH cte AS
    (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY FID ORDER BY FID, YearS) AS NUMBER
    FROM Table2
    )

SELECT t.ID, MAX(CASE WHEN cte.number = 1 THEN cte.YearS END) as yr1, MAX(CASE WHEN cte.number = 1 THEN cte.Val END) as val1,
MAX(CASE WHEN cte.number = 2 THEN cte.YearS END) as yr2, MAX(CASE WHEN cte.number = 2 THEN cte.Val END) as val2,
MAX(CASE WHEN cte.number = 3 THEN cte.YearS END) as yr3, MAX(CASE WHEN cte.number = 3 THEN cte.Val END) as val3,
MAX(CASE WHEN cte.number = 4 THEN cte.YearS END) as yr4, MAX(CASE WHEN cte.number = 4 THEN cte.Val END) as val4
FROM Table1 T
JOIN cte ON t.ID = cte.FID
GROUP BY t.ID

Answer 2

See: Using SQL Server 2005/2008 Pivot on Unknown Number of Columns

Answer 3

Hi rs,

I have created a table called "Table2", containing the data you have shown above under your Table 2 heading.

Here is the SQL I used in SQL Server 2008.

WITH RankedValues AS
(
    SELECT
        FID AS ID,
        YearS,
        ROW_NUMBER() OVER(PARTITION BY FID ORDER BY YearS) AS YearSRank,
        Val
    FROM
        Table2
)
SELECT
    ID,
    MAX((CASE WHEN YearSRank = 1 THEN YearS ELSE 0 END)) AS Yr1,
    MAX((CASE WHEN YearSRank = 1 THEN Val ELSE '' END)) AS Val1,
    MAX((CASE WHEN YearSRank = 2 THEN YearS ELSE 0 END)) AS Yr2,
    MAX((CASE WHEN YearSRank = 2 THEN Val ELSE '' END)) AS Val2,
    MAX((CASE WHEN YearSRank = 3 THEN YearS ELSE 0 END)) AS Yr3,
    MAX((CASE WHEN YearSRank = 3 THEN Val ELSE '' END)) AS Val3,
    MAX((CASE WHEN YearSRank = 4 THEN YearS ELSE 0 END)) AS Yr4,
    MAX((CASE WHEN YearSRank = 4 THEN Val ELSE '' END)) AS Val4
FROM
    RankedValues
GROUP BY
    ID

The above SQL will result in this:

ID   Yr1     Val1  Yr2     Val2    Yr3     Val3  Yr4     Val4
---------------------------------------------------------------------
1    2008    Up    2009    Down    2010    Up    0    
2    2000    Up    2001    Down    2002    Up    2003    Up
3    2009    Down  2010    Up      0             0    

The reason you do not see NULL values is because of the ELSE in each CASE statement. If you rather have NULL values, simply remove the ELSE 0 and ELSE '' as required.

I do not know at this time if it is possible to make this generic, e.g.: process an unknown amount of distinct FIDs, as this would also mean generating the column names (Yr1, al1, Yr2,etc..) generically.

You could propably achieve this with dynamic SQL but as I'm not a big fan of dynamic SQL I would try and look into another way of dealing with that.

-- Edit (Added pivot approach for completness)--

I looked at the link Joe Stefanelli posted and I added the SQL below for your requirement. Though I do not like the idea of dynamic SQL I was unable to find any other way in this specific instance.

DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)

SELECT @years = STUFF((
    SELECT DISTINCT
        '],[' + ltrim(str(YearS))
    FROM Table2
    ORDER BY '],[' + ltrim(str(YearS))
    FOR XML PATH('')), 1, 2, '') + ']'

SET @query =
    'SELECT * FROM
    (
        SELECT FID AS ID,YearS,Val
        FROM Table2
    ) AS t
    PIVOT (MAX(Val) FOR YearS IN (' + @years + ')) AS pvt'

EXECUTE (@query)

This will result in the follwing:

ID  2000    2001    2002    2003    2008    2009    2010
---------------------------------------------------------
1   NULL    NULL    NULL    NULL    Up      Down    Up
2   Up      Down    Up      Up      NULL    NULL    NULL
3   NULL    NULL    NULL    NULL    NULL    Down    Up

Depending which format and approach you like best, at least you have your options lined out.