SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Combine values in multiple fields into 1 field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vasylus
Starting Member

USA
3 Posts

Posted - 11/04/2012 :  11:59:33  Show Profile  Reply with Quote
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 MAR
USA 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 VALUE
USA 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

USA
3 Posts

Posted - 11/04/2012 :  12:04:50  Show Profile  Reply with Quote
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 SQL


Sub 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/04/2012 :  12:16:20  Show Profile  Reply with Quote
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 MAR
USA 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 VALUE
USA 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,
MonthVal
FROM
(
SELECT CNTRY,
METRIC,
JAN AS MonthVal,
1 AS Ord
FROM Table

UNION ALL

SELECT CNTRY,
METRIC,
FEB,
2
FROM Table

UNION ALL

SELECT CNTRY,
METRIC,
MAR,
3 
FROM table
)t
ORDER BY CNTRY,
METRIC,
Ord


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/04/2012 :  12:37:44  Show Profile  Reply with Quote
or if its SQL 2005 or above then


SELECT *
FROM Table t
UNPIVOT (Val FOR MonthVal IN ([JAN],[FEB],[MAR]))u


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vasylus
Starting Member

USA
3 Posts

Posted - 11/04/2012 :  13:46:11  Show Profile  Reply with Quote
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$] t
UNPIVOT (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, MonthVal
Jan, 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!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/04/2012 :  13:51:53  Show Profile  Reply with Quote
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$] t
UNPIVOT (Val FOR MonthVal IN ([JAN],[FEB],[MAR]))u


you cant directly pull sql query from an Excel file. You should be using OPENROWSET for that
see
http://support.microsoft.com/kb/321686


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, MonthVal
Jan, 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 columns


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?



for that you need two step solution
one UNPIVOT to get months as values in column
then a PIVOT to get them further as columns based on year

Thanks again!!!



see replies inline

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000