August 13, 2012 at 10:26 am
Hi!
I need some help with Select statement that I try to build.
My table structure is:
ID, DocID, MilestoneID, Baseline, EstimDate
1 1 1 0 Date1
2 1 2 0 Date2
3 1 3 0 Date3
4 1 1 1 Date4
5 1 2 1 Date5
6 1 3 1 Date6
I need to make a view that would look like
DocID, MilestoneID, Baseline0, Baseline1
1 1 Date1 Date4
1 2 Date2 Date5
1 3 Date3 Date6
I assume some kind of pivot queries should be used, but i am totally new to this, so I need some expert help:-)
Thanks
August 13, 2012 at 10:29 am
Will it always be two columns (0 & 1), or will it be variable?
If variable, search online for "t-sql dynamic cross-tab" or "t-sql dynamic pivot". You'll find good articles, with example code, for those concepts. That's probably what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 10:38 am
Thanks for the reply.
In this case it is fixed number of Baseline columns.
August 13, 2012 at 11:32 am
This looks similar to this post:
http://qa.sqlservercentral.com/Forums/Topic1343554-391-1.aspx
--Vadim R.
August 13, 2012 at 11:34 am
Are you familiar with Common Table Expressions (CTEs) and the ranking functions (Row_Number() to be specific)?
Here's a generic sample for pivoting a fixed number of columns:
with CTE as
(select *,
Row_Number() over (Partition by DocID, MilestoneID Order by Date) as R
from dbo.MyTable)
select C1.DocID, C1.Date as Date1, C2.Date as Date2, C3.Date as Date3
from CTE as C1
left outer join CTE as C2
on C1.DocID = C2.DocID
and C1.MilestoneID = C2.MilestoneID
and C2.R = 2
left outer join CTE as C3
on C1.DocID = C3.DocID
and C1.MilestoneID = C3.MilestoneID
and C3.R = 3
Where C1.R = 1;
Keep adding left outer joins to the query till you get the number of columns you need.
That's only a good solution if you really do have a fixed number of columns.
There are other solutions that will get the same result, this one has the main advantage of being easy to read once you understand CTEs and ranking functions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 12:06 pm
Here is another possible solution. The key here is simple, test, test, and test again to find the correct solution to you problem.
CREATE TABLE dbo.testtab(
ID INT,
DocID INT,
MilestoneID INT,
Baseline INT,
EstimDate CHAR(5)
);
GO
INSERT INTO dbo.testtab(ID,DocID,MilestoneID,Baseline,EstimDate)
VALUES (1,1,1,0,'Date1'),
(2,1,2,0,'Date2'),
(3,1,3,0,'Date3'),
(4,1,1,1,'Date4'),
(5,1,2,1,'Date5'),
(6,1,3,1,'Date6');
GO
SELECT
ID,
DocID,
MilestoneID,
Baseline,
EstimDate
FROM
dbo.testtab;
GO
WITH BaseData AS (
SELECT DISTINCT
DocID,
MilestoneID
FROM
dbo.testtab
)
SELECT
*
FROM
BaseData bd
OUTER APPLY (SELECT Baseline, EstimDate FROM dbo.testtab tt1 WHERE tt1.DocID = bd.DocID AND tt1.MilestoneID = bd.MilestoneID AND tt1.Baseline = 0)dt1(Baseline,EstimDate)
OUTER APPLY (SELECT Baseline, EstimDate FROM dbo.testtab tt2 WHERE tt2.DocID = bd.DocID AND tt2.MilestoneID = bd.MilestoneID AND tt2.Baseline = 1)dt2(Baseline,EstimDate);
GO
INSERT INTO dbo.testtab(ID,DocID,MilestoneID,Baseline,EstimDate)
VALUES (7,2,1,0,'Date1'),
(8,2,2,0,'Date2'),
(9,2,1,1,'Date4'),
(10,2,2,1,'Date5'),
(11,2,3,1,'Date6');
GO
WITH BaseData AS (
SELECT DISTINCT
DocID,
MilestoneID
FROM
dbo.testtab
)
SELECT
* -- Should explicitly list the columns
FROM
BaseData bd
OUTER APPLY (SELECT Baseline, EstimDate FROM dbo.testtab tt1 WHERE tt1.DocID = bd.DocID AND tt1.MilestoneID = bd.MilestoneID AND tt1.Baseline = 0)dt1(Baseline,EstimDate)
OUTER APPLY (SELECT Baseline, EstimDate FROM dbo.testtab tt2 WHERE tt2.DocID = bd.DocID AND tt2.MilestoneID = bd.MilestoneID AND tt2.Baseline = 1)dt2(Baseline,EstimDate);
GO
-- clean up the sandbox
DROP TABLE dbo.testtab;
GO
August 13, 2012 at 8:09 pm
rVadim (8/13/2012)
This looks similar to this post:http://qa.sqlservercentral.com/Forums/Topic1343554-391-1.aspx
Patterning after the quoted article, the solution for your data is as follows:
CREATE TABLE #testtab(
ID INT, DocID INT, MilestoneID INT, Baseline INT, EstimDate CHAR(5));
INSERT INTO #testtab(ID,DocID,MilestoneID,Baseline,EstimDate)
VALUES (1,1,1,0,'Date1'), (2,1,2,0,'Date2'), (3,1,3,0,'Date3'),
(4,1,1,1,'Date4'), (5,1,2,1,'Date5'), (6,1,3,1,'Date6');
SELECT DocID, MilestoneID
,Baseline0=MAX(CASE WHEN Baseline = 0 THEN EstimDate ELSE NULL END)
,Baseline1=MAX(CASE WHEN Baseline = 1 THEN EstimDate ELSE NULL END)
FROM #testtab
GROUP BY DocID, MilestoneID
DROP TABLE #testtab
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply