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 

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

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

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

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!!!

---