Post reply

pivot

  • September 15, 2011 at 6:49 am

    #390414

    The stored procedure (sp) returns fields as follows:

    YearQuarter Field1 Field2 Field3 Field4

    null 5.33 5.6 4.2 5

    2010 -1.43 -3.5 2.54 3.1

    Q2 2010 2.5 4.3 2.2 8.1

    Q1 2011 -2.4 9.4 4.3 1.2

    How can I show this as follows please?

    null 2010 Q2 2010 Q1 2011

    Field1 5.33 -1.43 2.5 -2.4

    Field2 5.6 -3.5 4.3 9.4

    Field3 4.2 2.54 2.2 4.3

    Field4 5 3.1 8.1 1.2

  • September 15, 2011 at 7:56 am

    #1381980

    Your sample data doesn't seem to be correct: you have a different number of columns in each row.

    Try posting your sample data in this form:

    WITH SampleData (Quarter, Year, Field1, Field2, Field3, Field4)

    AS (

    SELECT null, null, 5.33, 5.6, 4.2, 5

    UNION ALL SELECT null, 2010, -1.43, -3.5, 2.54, 3.1

    UNION ALL SELECT 'Q2', 2010, 2.5, 4.3, 2.2, 8.1

    UNION ALL SELECT 'Q1', 2011, -2.4, 9.4, 4.3, 1.2

    )

    SELECT *

    FROM SampleData

    -- Gianluca Sartori

  • September 15, 2011 at 8:00 am

    #1381985

    Note that the data is dynamic and so can change, so this solution does NOT help.

    Thanks

  • September 15, 2011 at 8:10 am

    #1381990

    Re-read my post: I didn't offer any solution, I just asked you to fix your sample data, as it appears to be mangled.

    If you post sample data, I can post a solution.

    -- Gianluca Sartori

  • September 15, 2011 at 8:16 am

    #1381996

    The data is as below:

    The stored procedure (sp) returns fields as follows:

    YearQuarter Field1 Field2 Field3 Field4

    null 5.33 5.6 4.2 5

    2010 -1.43 -3.5 2.54 3.1

    Q2 2010 2.5 4.3 2.2 8.1

    Q1 2011 -2.4 9.4 4.3 1.2

    How can I show this as follows please?

    null 2010 Q2 2010 Q1 2011

    Field1 5.33 -1.43 2.5 -2.4

    Field2 5.6 -3.5 4.3 9.4

    Field3 4.2 2.54 2.2 4.3

    Field4 5 3.1 8.1 1.2

  • September 15, 2011 at 8:30 am

    #1382002

    You posted the same exact sample data as before.

    If you post you sample data the way I did, everything is clearer and faster for us to help you.

    "Q2 2010" is a single value or what? Is it correct laid out this way?

    YearQuarter Field1 Field2 Field3 Field4

    null 5.33 5.6 4.2 5

    2010 -1.43 -3.5 2.54 3.1

    Q2 2010 2.5 4.3 2.2 8.1

    Q1 2011 -2.4 9.4 4.3 1.2

    -- Gianluca Sartori

  • September 15, 2011 at 8:48 am

    #1382025

    Apologiese, not sure why my format did not come out the way you asked for.

    Anyway, yes the format you posted is what it shuld be.

    And Q2 2010 is indeed a single value.

    Thanks

  • September 15, 2011 at 8:53 am

    #1382031

    If you took the time to know how to post queries on this forum, you would know ... Its the link in Gianluca's signature. It explains how people should post here. It's the only way to get answers fast.

    How To Post[/url]

  • September 15, 2011 at 9:18 am

    #1382046

    vick.ram79 (9/15/2011)

    If you took the time to know how to post queries on this forum, you would know ... Its the link in Gianluca's signature. It explains how people should post here. It's the only way to get answers fast.

    No worries, he will get it right next time.

    -- Gianluca Sartori

  • September 15, 2011 at 9:20 am

    #1382052

    I think this should do the trick:

    DECLARE @sampleData TABLE (

    YearQuarter varchar(7),

    Field1 decimal(21,6),

    Field2 decimal(21,6),

    Field3 decimal(21,6),

    Field4 decimal(21,6)

    )

    INSERT INTO @sampleData

    SELECT null, 5.33, 5.6, 4.2, 5

    UNION ALL SELECT '2010', -1.43, -3.5, 2.54, 3.1

    UNION ALL SELECT 'Q2 2010', 2.5, 4.3, 2.2, 8.1

    UNION ALL SELECT 'Q1 2011', -2.4, 9.4, 4.3, 1.2

    SELECT *

    FROM (

    SELECT ISNULL(YearQuarter,'NULL') AS YearQuarter, Field1, Field2, Field3, Field4

    FROM @sampleData

    ) AS s

    UNPIVOT ( value FOR name IN ([Field1],[Field2],[Field3],[Field4]) ) AS u

    PIVOT (min(value) FOR YearQuarter IN ([NULL], [2010], [Q2 2010], [Q1 2011] ) ) AS p

    The output column names must be specified as constants in the pivot query. If this does not fit in your problem, we can try to solve it with some dynamic sql.

    -- Gianluca Sartori

  • September 15, 2011 at 9:29 am

    #1382061

    This should help you when output column names (YearQuarter values) are not known from the start:

    IF OBJECT_ID('tempdb..#sampleData') IS NOT NULL

    DROP TABLE #sampleData

    CREATE TABLE #sampleData (

    YearQuarter varchar(7),

    Field1 decimal(21,6),

    Field2 decimal(21,6),

    Field3 decimal(21,6),

    Field4 decimal(21,6)

    )

    INSERT INTO #sampleData

    SELECT NULL, 5.33, 5.6, 4.2, 5

    UNION ALL SELECT '2010', -1.43, -3.5, 2.54, 3.1

    UNION ALL SELECT 'Q2 2010', 2.5, 4.3, 2.2, 8.1

    UNION ALL SELECT 'Q1 2011', -2.4, 9.4, 4.3, 1.2

    DECLARE @sql nvarchar(max)

    SET @sql = STUFF((

    SELECT DISTINCT ',' + QUOTENAME(ISNULL(YearQuarter,'NULL')) AS [text()]

    FROM #sampleData

    ORDER BY 1

    FOR XML PATH('')

    ), 1, 1, SPACE(0))

    SET @sql = '

    SELECT *

    FROM (

    SELECT ISNULL(YearQuarter,''NULL'') AS YearQuarter, Field1, Field2, Field3, Field4

    FROM #sampleData

    ) AS s

    UNPIVOT ( value FOR name IN ([Field1],[Field2],[Field3],[Field4]) ) AS u

    PIVOT (MIN(value) FOR YearQuarter IN ('+ @sql +') ) AS p

    '

    EXEC(@sql)

    -- Gianluca Sartori

  • September 15, 2011 at 9:29 am

    #1382063

    Thanks

  • September 15, 2011 at 9:32 am

    #1382064

    Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply