March 22, 2012 at 9:11 am
Given this table data;
ID StartDate EndDate
239 2011-05-05 00:00:00.000 2011-07-21 00:00:00.000
239 2011-10-04 00:00:00.000 NULL
264 2011-05-12 00:00:00.000 2011-06-27 00:00:00.000
246 NULL NULL
247 2011-06-16 00:00:00.000 2011-09-28 00:00:00.000
257 NULL NULL
267 2011-11-15 00:00:00.000 NULL
269 2011-09-28 00:00:00.000 NULL
299 2011-05-12 00:00:00.000 2011-05-13 00:00:00.000
300 2011-05-12 00:00:00.000 2011-05-13 00:00:00.000
300 2011-05-12 00:00:00.000 2011-10-21 00:00:00.000
How can i run a select statement to show the results as such;
ID StartDate EndDate StartDate2 EndDate2
239 2011-05-05 00:00:00.000 2011-07-21 00:00:00.000 2011-10-04 00:00:00.000 NULL
264 2011-05-12 00:00:00.000 2011-06-27 00:00:00.000
246 NULL NULL
247 2011-06-16 00:00:00.000 2011-09-28 00:00:00.000
257 NULL NULL
267 2011-11-15 00:00:00.000 NULL
269 2011-09-28 00:00:00.000 NULL
299 2011-05-12 00:00:00.000 2011-05-13 00:00:00.000
300 2011-05-12 00:00:00.000 2011-05-13 00:00:00.000 2011-05-12 00:00:00.000 2011-10-21 00:00:00.000
March 22, 2012 at 9:16 am
Geoff you have been here quite long enuf to know how to post the questions ! Please follow the etiquettes mentioned in this article and provide us data in consumable format http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2012 at 9:25 am
The answer to your question is:
YES YOU CAN!
Could you please post in a proper way?
Or to make it right will take you more time than to find a solution by yourself? :hehe:
March 22, 2012 at 9:32 am
Actually, the correct answer (assuming that your date fields are actual date fields) is that it's IMPOSSIBLE. Your expected output violates first normal form, because some of the rows have five columns and some have three columns. You cannot produce output that violates first normal form in T-SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 22, 2012 at 9:37 am
ok, ok . sorry guys, crazy morning....
declare @mytable table (ID int, StartDate datetime, EndDate datetime)
insert into @mytable
select '239', '05/05/2011','07/21/2011'
UNION ALL
select '239', '10/04/2011', NULL
UNION ALL
select '240', '06/14/2010', NULL
UNION ALL
select '264', '06/16/2011', '09/28/2011'
UNION ALL
select '300', '05/12/2011', '05/13/2011'
UNION ALL
select '300', '05/12/2011', '10/21/2011'
select * from @mytable
how to get these results to span multiple dynamic columns on ID number?
March 22, 2012 at 9:51 am
How many number of rows are possible for an ID ? is it alwyas 2 or it may vary dynamically ? As Drew said, it violated 1NF, the output will look klunky 🙁
March 22, 2012 at 9:55 am
If the maximum number of Start/EndDate combinations is definite, then you can do something like this:
declare @mytable table (ID int, StartDate datetime, EndDate datetime)
insert into @mytable
select '239', '05/05/2011','07/21/2011'
UNION ALL
select '239', '10/04/2011', NULL
UNION ALL
select '240', '06/14/2010', NULL
UNION ALL
select '264', '06/16/2011', '09/28/2011'
UNION ALL
select '300', '05/12/2011', '05/13/2011'
UNION ALL
select '300', '05/12/2011', '10/21/2011'
;with cteRN
AS
(
select ID, StartDate, EndDate, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StartDate) RN
from @mytable
)
SELECT ID
,MAX(CASE WHEN RN = 1 THEN StartDate ELSE NULL END) AS StartDate
,MAX(CASE WHEN RN = 1 THEN EndDate ELSE NULL END) AS EndDate
,MAX(CASE WHEN RN = 2 THEN StartDate ELSE NULL END) AS StartDate2
,MAX(CASE WHEN RN = 2 THEN EndDate ELSE NULL END) AS EndDate2
FROM cteRN
GROUP BY ID
If a maximum number of Start/EndDate combinations is unknown, you need to search for "dynamic cross-tab"
March 22, 2012 at 2:43 pm
thanks for the help everyone. my final code looked like this;
create table #mytable (ID int, StartDate datetime, EndDate datetime)
insert into #mytable
select '239', '05/05/2011','07/21/2011'
UNION ALL
select '239', '10/04/2011', NULL
UNION ALL
select '240', '06/14/2010', NULL
UNION ALL
select '264', '06/16/2011', '09/28/2011'
UNION ALL
select '300', '05/12/2011', '05/13/2011'
UNION ALL
select '300', '05/12/2011', '10/21/2011'
declare @maxCount int
; with MAX_CTE as (
select COUNT(ID) as MaxCount from #mytable
group by ID)
select @maxCount = (select MAX(MaxCount) from MAX_CTE)
declare @sql varchar(MAX), @loopint int
set @loopint =1
set @sql = ';
with cteRN
AS
(
select ID, StartDate, EndDate, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StartDate) RN
from #mytable
)
SELECT ID
'
WHILE @maxCount > 0
begin
,MAX(CASE WHEN RN = '+CONVERT(VARCHAR(10),@loopint)+' THEN isnull(convert(varchar(10),StartDate,101),''NoEndDate'') ELSE '''' END) AS [StartDate'+CONVERT(VARCHAR(10),@loopint)+']
,MAX(CASE WHEN RN = '+CONVERT(VARCHAR(10),@loopint)+' THEN isnull(convert(varchar(10),EndDate,101),''NoEndDate'') ELSE '''' END) AS [EndDate'+CONVERT(VARCHAR(10),@loopint)+']
'
set @maxCount = @maxCount-1
set @loopint = @loopint +1
end
FROM cteRN
GROUP BY ID'
exec(@SQL)
drop table #mytable
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply