April 1, 2013 at 1:43 am
Hello,
I have a table like this
CREATE TABLE [temp](
[Country] [varchar](200) NULL,
[Country_Group] [varchar](200) NULL,
[Year] [varchar](200) NULL,
[R1_TV] [decimal](38, 2) NULL,
[R2_TV] [decimal](38, 2) NULL,
[R3_TV] [decimal](38, 2) NULL,
[R1_Google] [decimal](38, 2) NULL,
[R2_Google] [decimal](38, 2) NULL,
[R3_Google] [decimal](38, 2) NULL,
[R1_Yahoo] [decimal](38, 2) NULL,
[R2_Yahoo] [decimal](38, 2) NULL
) ON [PRIMARY]
and data is following
INSERT [temp] ([Country], [Country_Group], [Year], [R1_TV], [R2_TV], [R3_TV], [R1_Google], [R2_Google], [R3_Google], [R1_Yahoo], [R2_Yahoo]) VALUES (N'Greece', N'Test111', N'2011', CAST(3304.24 AS Decimal(38, 2)), CAST(5.56 AS Decimal(38, 2)), CAST(30378.95 AS Decimal(38, 2)), CAST(568.23 AS Decimal(38, 2)), CAST(4.25 AS Decimal(38, 2)), CAST(56458.00 AS Decimal(38, 2)), CAST(2456.00 AS Decimal(38, 2)), CAST(6.20 AS Decimal(38, 2)))
INSERT [temp] ([Country], [Country_Group], [Year], [R1_TV], [R2_TV], [R3_TV], [R1_Google], [R2_Google], [R3_Google], [R1_Yahoo], [R2_Yahoo]) VALUES (N'Ukraine', N'Test111', N'2011', CAST(792.12 AS Decimal(38, 2)), CAST(6.11 AS Decimal(38, 2)), CAST(32391.28 AS Decimal(38, 2)), CAST(456.14 AS Decimal(38, 2)), CAST(6.69 AS Decimal(38, 2)), CAST(78562.00 AS Decimal(38, 2)), CAST(3652.00 AS Decimal(38, 2)), CAST(736.00 AS Decimal(38, 2)))
INSERT [temp] ([Country], [Country_Group], [Year], [R1_TV], [R2_TV], [R3_TV], [R1_Google], [R2_Google], [R3_Google], [R1_Yahoo], [R2_Yahoo]) VALUES (N'ZZZZZZZZZZ', N'Test111', N'2011', CAST(2007.91 AS Decimal(38, 2)), CAST(5.66 AS Decimal(38, 2)), CAST(62770.22 AS Decimal(38, 2)), CAST(785.25 AS Decimal(38, 2)), CAST(3.21 AS Decimal(38, 2)), CAST(35412.00 AS Decimal(38, 2)), CAST(5214.00 AS Decimal(38, 2)), CAST(3.12 AS Decimal(38, 2)))
Now I want to Unpivot it like this
CREATE TABLE [tt](
[Country_Group] [varchar](200) NULL,
[Country] [varchar](200) NULL,
[Year] [varchar](200) NULL,
[Media] [nvarchar](4000) NULL,
[R1] [decimal](38, 2) NULL,
[R2] [decimal](38, 2) NULL,
[R3] [decimal](38, 2) NULL
) ON [PRIMARY]
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Ukraine', N'2011', N'TV', CAST(792.12 AS Decimal(38, 2)), CAST(6.11 AS Decimal(38, 2)), CAST(32391.28 AS Decimal(38, 2)))
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Ukraine', N'2011', N'Google', CAST(456.14 AS Decimal(38, 2)), CAST(6.69 AS Decimal(38, 2)), CAST(78562.00 AS Decimal(38, 2)))
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Ukraine', N'2011', N'Yahoo', CAST(3652.00 AS Decimal(38, 2)), CAST(736.00 AS Decimal(38, 2)), CAST(0.00 AS Decimal(38, 2)))
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Greece', N'2011', N'TV', CAST(792.12 AS Decimal(38, 2)), CAST(6.11 AS Decimal(38, 2)), CAST(32391.28 AS Decimal(38, 2)))
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Greece', N'2011', N'Google', CAST(456.14 AS Decimal(38, 2)), CAST(6.69 AS Decimal(38, 2)), CAST(78562.00 AS Decimal(38, 2)))
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Greece', N'2011', N'Yahoo', CAST(3652.00 AS Decimal(38, 2)), CAST(736.00 AS Decimal(38, 2)), CAST(0.00 AS Decimal(38, 2)))
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'ZZZZZZZZZZ', N'2011', N'TV', CAST(792.12 AS Decimal(38, 2)), CAST(6.11 AS Decimal(38, 2)), CAST(32391.28 AS Decimal(38, 2)))
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'ZZZZZZZZZZ', N'2011', N'Google', CAST(456.14 AS Decimal(38, 2)), CAST(6.69 AS Decimal(38, 2)), CAST(78562.00 AS Decimal(38, 2)))
INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'ZZZZZZZZZZ', N'2011', N'Yahoo', CAST(3652.00 AS Decimal(38, 2)), CAST(736.00 AS Decimal(38, 2)), CAST(0.00 AS Decimal(38, 2)))
Please help
April 1, 2013 at 8:23 am
Your data is not normalized and your results are not normalized. The reason this is difficult is because your structures make this difficult.
At any rate, you did a great job posting ddl, sample data and desired output. This work on your sample data.
select Country_Group, Country, YEAR, Media, R1, R2, R3
from
(
select Country_Group, Country, YEAR, 'TV' as Media, R1_TV as R1, R2_TV as R2, R3_TV as R3, 1 as SortOrder from temp
union all
select Country_Group, Country, YEAR, 'Google', R1_Google, R2_Google, R3_Google, 2 from temp
union all
select Country_Group, Country, YEAR, 'Yahoo', R1_Yahoo, R2_Yahoo, null, 3 from temp
) x
order by Country_Group, Country, SortOrder
_______________________________________________________________
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/
April 1, 2013 at 4:21 pm
Sean Lange (4/1/2013)
Your data is not normalized and your results are not normalized. The reason this is difficult is because your structures make this difficult.
Heh... I can't help wondering if that's precisely what he's trying to do. Normalize the data in a new table.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2013 at 7:35 am
Jeff Moden (4/1/2013)
Sean Lange (4/1/2013)
Your data is not normalized and your results are not normalized. The reason this is difficult is because your structures make this difficult.Heh... I can't help wondering if that's precisely what he's trying to do. Normalize the data in a new table.
If that is the case then I would recommend normalizing these results or it isn't much of an improvement.
_______________________________________________________________
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/
April 2, 2013 at 7:48 am
Sean Lange (4/2/2013)
Jeff Moden (4/1/2013)
Sean Lange (4/1/2013)
Your data is not normalized and your results are not normalized. The reason this is difficult is because your structures make this difficult.Heh... I can't help wondering if that's precisely what he's trying to do. Normalize the data in a new table.
If that is the case then I would recommend normalizing these results or it isn't much of an improvement.
I may be wrong, but it does look like he is based on the the table he wants compared to what he has right now.
April 2, 2013 at 8:00 am
Lynn Pettis (4/2/2013)
Sean Lange (4/2/2013)
Jeff Moden (4/1/2013)
Sean Lange (4/1/2013)
Your data is not normalized and your results are not normalized. The reason this is difficult is because your structures make this difficult.Heh... I can't help wondering if that's precisely what he's trying to do. Normalize the data in a new table.
If that is the case then I would recommend normalizing these results or it isn't much of an improvement.
I may be wrong, but it does look like he is based on the the table he wants compared to what he has right now.
Up until they need to add an R4 column.
_______________________________________________________________
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy