Pivot with NVARCHAR data

  • Hi,

    I have a probelm with Pivot when table have nvarchar data. Please refer the following tables.

    #Table1 should be convert into #Table2.

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

    BEGIN

    DROP TABLE #Table1

    END

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

    BEGIN

    DROP TABLE #Table2

    END

    SELECT ID,[MonthName],[Value1],[Value2],[Value3],[Value4]

    INTO #Table1

    FROM (--==== Test data

    SELECT 1,'Jan',10,20,'ABCD',30 UNION ALL

    SELECT 1,'Feb',34,12,'KAJDS',87 UNION ALL

    SELECT 1,'March',65,43,'SDFS',64 UNION ALL

    SELECT 1,'April',23,34,'ZDFSD',98

    )d(ID,[MonthName],[Value1],[Value2],[Value3],[Value4])

    ;

    SELECT ID,[Jan-Value1],[Jan-Value2],[Jan-Value3],[Jan-Value4],[Feb-Value1],[Feb-Value2],[Feb-Value3],[Feb-Value4],[March-Value1],[March-Value2],[March-Value3],[March-Value4]

    ,[April-Value1],[April-Value2],[April-Value3],[April-Value4]

    INTO #Table2

    FROM (--==== Test data

    SELECT 1,10,20,'ABCD',30,34,12,'KAJDS',87,65,43,'SDFS',64,23,34,'ZDFSD',98

    )E(ID,[Jan-Value1],[Jan-Value2],[Jan-Value3],[Jan-Value4],[Feb-Value1],[Feb-Value2],[Feb-Value3],[Feb-Value4],[March-Value1],[March-Value2],[March-Value3],[March-Value4]

    ,[April-Value1],[April-Value2],[April-Value3],[April-Value4])

    ;

    SELECT * FROM #Table1

    SELECT * FROM #Table2

    Thanks in advance,

    Vijay

  • Thank you so much for posting ddl and sample data. This makes it very easy to work on your issue.

    What you need here is a crosstab. I made some assumptions about your real data. I assume the value for ID changes and you want the results to split when that value changes. I also had to add a column so there was something to use as an order by. The sample you posted had nothing to work with there.

    This will work with the sample data you posted.

    with MySortedData as

    (

    SELECT *, ROW_NUMBER() over (partition by ID order by cast(left(MonthName, 3) + ' 1 2014' as date)) as RowNum FROM #Table1

    )

    select ID,

    MAX(case when RowNum = 1 then Value1 end) as [Jan-Value1],

    MAX(case when RowNum = 1 then Value2 end) as [Jan-Value2],

    MAX(case when RowNum = 1 then Value3 end) as [Jan-Value3],

    MAX(case when RowNum = 1 then Value4 end) as [Jan-Value4],

    MAX(case when RowNum = 2 then Value1 end) as [Feb-Value1],

    MAX(case when RowNum = 2 then Value2 end) as [Feb-Value2],

    MAX(case when RowNum = 2 then Value3 end) as [Feb-Value3],

    MAX(case when RowNum = 2 then Value4 end) as [Feb-Value4],

    MAX(case when RowNum = 3 then Value1 end) as [March-Value1],

    MAX(case when RowNum = 3 then Value2 end) as [March-Value2],

    MAX(case when RowNum = 3 then Value3 end) as [March-Value3],

    MAX(case when RowNum = 3 then Value4 end) as [March-Value4],

    MAX(case when RowNum = 4 then Value1 end) as [April-Value1],

    MAX(case when RowNum = 4 then Value2 end) as [April-Value2],

    MAX(case when RowNum = 4 then Value3 end) as [April-Value3],

    MAX(case when RowNum = 4 then Value4 end) as [April-Value4]

    from MySortedData

    group by ID

    You can read more about crosstabs by following the links in my signature.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 2 posts - 1 through 2 (of 2 total)

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