November 19, 2014 at 8:02 am
Hi all,
I've a table with two columns:
ColumnA ColumnB
1 88
2 99
3 112
I want to present the data as follows:
1 2 3
88 99 112
So I want to use the values of column 1 as different columns and the value of columnB as values of the new columns. I tried with unpivot but no success. Any suggestions for this issue.
Thanks in advance.
November 19, 2014 at 8:16 am
Quick cross-tab suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA (ColumnA,ColumnB) AS
(
SELECT 1 AS ColumnA,88 AS ColumnB UNION ALL
SELECT 2, 99 UNION ALL
SELECT 3, 112
)
SELECT
MAX(CASE WHEN SD.ColumnA = 1 THEN ColumnB END) AS C1
,MAX(CASE WHEN SD.ColumnA = 2 THEN ColumnB END) AS C2
,MAX(CASE WHEN SD.ColumnA = 3 THEN ColumnB END) AS C3
FROM SAMPLE_DATA SD;
Results
C1 C2 C3
----------- ----------- -----------
88 99 112
November 19, 2014 at 8:36 am
thank you! will do a small research on your solution! works for me
November 19, 2014 at 9:03 am
Trestire (11/19/2014)
thank you! will do a small research on your solution! works for me
No need to kill yourself doing research. Jeff Moden has written two excellent article on this very topic. The first is for static cross tabs like yours and the second is for dynamic cross tabs. Take a look at the two 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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply