October 31, 2013 at 1:40 pm
I'd like to put the data in table #Things into a normalized structure. Table #NewThings represents my desired output. I don't need to do any aggregation.
Thanks if you can help.
CREATE TABLE #Things
(
ID int,
Attribute1 varchar (1),
Attribute2 varchar (1),
OneMonthMetric1 int,
OneMonthMetric2 int,
TwoMonthMetric1 int,
TwoMonthMetric2 int,
ThreeMonthMetric1 int,
ThreeMonthMetric2 int
)
INSERT INTO #Things
(ID,Attribute1,Attribute2,OneMonthMetric1,OneMonthMetric2,TwoMonthMetric1,TwoMonthMetric2,ThreeMonthMetric1,ThreeMonthMetric2)
VALUES (1,'A','Z',33,1,4,3,12,3)
INSERT INTO #Things
(ID,Attribute1,Attribute2,OneMonthMetric1,OneMonthMetric2,TwoMonthMetric1,TwoMonthMetric2,ThreeMonthMetric1,ThreeMonthMetric2)
VALUES (2,'E','T',63,5,6,3,132,36)
INSERT INTO #Things
(ID,Attribute1,Attribute2,OneMonthMetric1,OneMonthMetric2,TwoMonthMetric1,TwoMonthMetric2,ThreeMonthMetric1,ThreeMonthMetric2)
VALUES (3,'P','O',5,50,64,3,99,16)
SELECT * FROM #Things
DROP TABLE #Things
CREATE TABLE #NewThings
(
ID int,
Attribute1 varchar (1),
Attribute2 varchar (1),
Period varchar(10),
Metric1 int,
Metric2 int
)
--1
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (1,'A','Z','OneMonth',33,1)
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (1,'A','Z','TwoMonth',4,3)
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (1,'A','Z','ThreeMonth',12,3)
--2
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (2,'E','T','OneMonth',63,5)
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (2,'E','T','TwoMonth',6,3)
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (2,'E','T','ThreeMonth',132,36)
--3
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (3,'A','Z','OneMonth',5,50)
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (3,'A','Z','TwoMonth',64,3)
INSERT INTO #NewThings
(ID,Attribute1,Attribute2,Period, Metric1,Metric2)
VALUES (3,'A','Z','ThreeMonth',99,16)
SELECT * FROM #NewThings
DROP TABLE #NewThings
October 31, 2013 at 1:53 pm
A method using CROSS APPLY could work for you.
More information in this article: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
SELECT ID, Attribute1, Attribute2, Period, Metric1,Metric2 FROM #Things
CROSS APPLY (VALUES('OneMonth',OneMonthMetric1,OneMonthMetric2),
('TwoMonth',TwoMonthMetric1,TwoMonthMetric2),
('ThreeMonth',ThreeMonthMetric1,ThreeMonthMetric2))x(Period, Metric1,Metric2)
October 31, 2013 at 1:55 pm
I should point out that #NewThings is itself not normalized (Metric1 and Metric2 should really be distinct rows in a separate table). That said, you can unpivot this data to achieve the data set you want. You could either do it in a single statement (like I provided below) or split them into separate statements and join them together (which might be a little easier to read)
select
ID,
Attribute1,
Attribute2,
Period = replace(period, 'Metric1', ''),
Metric1 = Value,
Metric2 = Value2
from (SELECT *
FROM #Things) src
unpivot (Value for Period in (OneMonthMetric1, TwoMonthMetric1, ThreeMonthMetric1)) upvt
unpivot (Value2 for Period2 in (OneMonthMetric2, TwoMonthmetric2, ThreeMonthMetric2)) upvt2
where replace(Period, 'Metric1', '') = replace(Period2, 'Metric2', '')
October 31, 2013 at 2:00 pm
Gabe T. (10/31/2013)
I should point out that #NewThings is itself not normalized (Metric1 and Metric2 should really be distinct rows in a separate table).
Agreed and thanks for the correction.
October 31, 2013 at 2:19 pm
Thank you both (and Dwain)!
October 31, 2013 at 4:16 pm
Gabe T. (10/31/2013)
I should point out that #NewThings is itself not normalized (Metric1 and Metric2 should really be distinct rows in a separate table).
Not necessarily if those 2 metrics represent different attributes such as width and height or lattitude and longitude or unit price and quantity. Putting in a separate table might give you a horrible EAV design which is not compliant with normalization forms.
In other words, it depends.:-D
October 31, 2013 at 4:27 pm
October 31, 2013 at 4:47 pm
I agreed with Gabe because I have the distinct advantage of seeing the actual data which would result in duplicate data. Since this is more of a reporting/data warehouse I may or may not break out into different tables.
Seeing enumerated columns like Metric1, Metric2, etc tends to understandably set off normalization alarms.
I guess a table with fields address1 and address2 is not normalized but accepted as falling within best practice.
November 3, 2013 at 6:58 pm
Chrissy321 (10/31/2013)
Thank you both (and Dwain)!
I'd have to say that's about the least work I ever had to apply to a forum question to get a thank you.
But you're welcome!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply