March 13, 2014 at 1:34 pm
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
March 13, 2014 at 3:30 pm
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply