Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
vasylus
Starting Member
3 Posts |
Posted - 2012-11-04 : 11:59:33
|
I am trying take numeric values in multiple fields (Jan-Mar) and combine them into a single column, while retaining their corresponding CNTRY and METRIC values.In other words, can I have every data point in fields JAN-MAR become its own row, as shown below? while keeping their CNTRY and METRIC values?So I'm looking to turn this...[Current]CNTRY METRIC JAN FEB MARUSA Metric1 97% 98% 89%USA Metric2 82% 83% 91%CAN Metric3 86% 83% 87%CAN Metric4 95% 99% 83%into this:[Desired]CNTRY METRIC MONTH VALUEUSA Metric1 Jan 97%USA Metric1 Feb 98%USA Metric1 Mar 89%USA Metric2 Jan 82%USA Metric2 Feb 83%USA Metric2 Mar 91%CAN Metric3 Jan 86%CAN Metric3 Feb 83%CAN Metric3 Mar 87%CAN Metric4 Jan 95%CAN Metric4 Feb 99%CAN Metric4 Mar 83%(Context: I want to link an Excel file that looks like Current, and link it to SRSS/Report Builder 3.0 and have it display like Desired.)I'm no expert yet in SQL, but I'm thinking a series of UNIONs might the right direction... Any help would be tremendously appreciated!!! |
|
vasylus
Starting Member
3 Posts |
Posted - 2012-11-04 : 12:04:50
|
For what it's worth, this is a VBA script that does much the same thing, though I can't wrap my head around how to get this functionality with SQLSub test() Dim j As Integer, k As Integer, m As Integer, x As Variant Worksheets("sheet2").Cells.Clear With Worksheets("sheet1") j = .Range("A1").End(xlDown).Row For k = 2 To j .Range("A" & k, "B" & k).Copy copying For m = 3 To 5 x = Array(.Cells(1, m), .Cells(k, m)) Dim r1 As Range With Worksheets("sheet2") Set r1 = .Cells(Rows.Count, "c").End(xlUp).Offset(1, 0) Range(r1, r1.Offset(0, 1)) = x End With Next m Next k End With End Sub credit to Venkat1926 at ozgrid.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-04 : 12:16:20
|
quote: Originally posted by vasylus I am trying take numeric values in multiple fields (Jan-Mar) and combine them into a single column, while retaining their corresponding CNTRY and METRIC values.In other words, can I have every data point in fields JAN-MAR become its own row, as shown below? while keeping their CNTRY and METRIC values?So I'm looking to turn this...[Current]CNTRY METRIC JAN FEB MARUSA Metric1 97% 98% 89%USA Metric2 82% 83% 91%CAN Metric3 86% 83% 87%CAN Metric4 95% 99% 83%into this:[Desired]CNTRY METRIC MONTH VALUEUSA Metric1 Jan 97%USA Metric1 Feb 98%USA Metric1 Mar 89%USA Metric2 Jan 82%USA Metric2 Feb 83%USA Metric2 Mar 91%CAN Metric3 Jan 86%CAN Metric3 Feb 83%CAN Metric3 Mar 87%CAN Metric4 Jan 95%CAN Metric4 Feb 99%CAN Metric4 Mar 83%(Context: I want to link an Excel file that looks like Current, and link it to SRSS/Report Builder 3.0 and have it display like Desired.)I'm no expert yet in SQL, but I'm thinking a series of UNIONs might the right direction... Any help would be tremendously appreciated!!!
SELECT CNTRY,METRIC,MonthValFROM(SELECT CNTRY,METRIC,JAN AS MonthVal,1 AS OrdFROM TableUNION ALLSELECT CNTRY,METRIC,FEB,2FROM TableUNION ALLSELECT CNTRY,METRIC,MAR,3 FROM table)tORDER BY CNTRY,METRIC,Ord ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-04 : 12:37:44
|
or if its SQL 2005 or above thenSELECT *FROM Table tUNPIVOT (Val FOR MonthVal IN ([JAN],[FEB],[MAR]))u ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
vasylus
Starting Member
3 Posts |
Posted - 2012-11-04 : 13:46:11
|
Hi Visakh! Thanks for the great replies! If you don't mind, I have some follow-up questions.1.) I am using SSRS/Report Builder 3.0, does that mean I have at least SQL 2005? If I'm linking Sheet1 from an excel file,SELECT *FROM [Sheet1$] tUNPIVOT (Val FOR MonthVal IN ([JAN],[FEB],[MAR]))u I get "Syntax error in FROM clause."2.) How can I change the SQL code to also return the month names next to the MonthVal? In other words, take the original names of the fields (Jan, Feb, Mar) and put them in a new field MonthName?MonthName, MonthValJan, 97%Feb, 98%Mar, 89%Jan, 82%Feb, 83%Mar, 91%Jan, 86%Feb, 83%Mar, 87%Jan, 95%Feb, 99%Mar, 83%3.) What if there were a new month field every month? So instead of Jan,Feb,Mar... say I had Sep12,Oct12,Nov12, and next month there would be Dec12, Jan13 etc. Is there a way to have the code open-ended enough to consolidate all fields to the right of METRIC the way you consolidated JAN, FEB, and MAR?Thanks again!!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-04 : 13:51:53
|
quote: Originally posted by vasylus Hi Visakh! Thanks for the great replies! If you don't mind, I have some follow-up questions.1.) I am using SSRS/Report Builder 3.0, does that mean I have at least SQL 2005? If I'm linking Sheet1 from an excel file,SELECT *FROM [Sheet1$] tUNPIVOT (Val FOR MonthVal IN ([JAN],[FEB],[MAR]))u you cant directly pull sql query from an Excel file. You should be using OPENROWSET for thatseehttp://support.microsoft.com/kb/321686I get "Syntax error in FROM clause."2.) How can I change the SQL code to also return the month names next to the MonthVal? In other words, take the original names of the fields (Jan, Feb, Mar) and put them in a new field MonthName?MonthName, MonthValJan, 97%Feb, 98%Mar, 89%Jan, 82%Feb, 83%Mar, 91%Jan, 86%Feb, 83%Mar, 87%Jan, 95%Feb, 99%Mar, 83%thats what UNPIVOT does. If you use suggestion I gave you'll get MonthVal as one of columns3.) What if there were a new month field every month? So instead of Jan,Feb,Mar... say I had Sep12,Oct12,Nov12, and next month there would be Dec12, Jan13 etc. Is there a way to have the code open-ended enough to consolidate all fields to the right of METRIC the way you consolidated JAN, FEB, and MAR?for that you need two step solutionone UNPIVOT to get months as values in columnthen a PIVOT to get them further as columns based on yearThanks again!!!
see replies inline------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|