You must Sign In to post a response.
  • Category: SQL Server

    How can i get columns in pivot

    Hi i have 10 years of record for each month wise ..i want display records in Pivot format.

    Ex:
    PName Period amount
    Prod1--- 01-01-2016 ---10
    Prod1--- 01-02-2016 ---11
    Prod1--- 01-03-2016 ---12
    Prod1--- 01-04-2016 ---13
    same prod1 records present for for 10 years.
    Prod2--- 01-01-2016 ---12
    Prod2--- 01-02-2016 ---13
    Prod2--- 01-03-2016 ---14
    Prod2--- 01-04-2016 ---15
    same prod2 for 10 years

    output should be

    PName--Jan 2016-- Feb 2016-- Mar 2016....Apr 16---for 10 years
    Prod1----10---------11----------12-----------13 so on....
    prod2-- --12---------13----------14-----------15 son on...

    is it possible to get the above output..
    let me know if need more clarification.

    Thanks
    Santosh
  • #764719
    Hi,

    Pls try this,
    DECLARE @cols AS nvarchar(max),
    @query AS nvarchar(max)

    SELECT
    @cols = STUFF((SELECT
    ',' + QUOTENAME(CONVERT(char(4), Period, 100) + CONVERT(char(4), Period, 120))
    FROM ProdYear
    GROUP BY Period
    ORDER BY Period
    FOR xml PATH (''), TYPE)
    .value('.', 'NVARCHAR(MAX)')
    , 1, 1, '')

    PRINT @Cols

    SET @query = 'SELECT PName,' + @cols + ' from
    (
    select PName, [Period], Amount
    from ProdYear
    ) x
    pivot
    (
    sum(Amount)
    for Period in (' + @cols + ')
    ) p '

    EXECUTE (@query)


Sign In to post your comments