how to dynamically create columns for single row result set.

Question

how to dynamically create columns for single row result set.

Geoff A

SSChampion

Points: 11511
More actions
March 22, 2012 at 9:11 am

#391843

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

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/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

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:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

drew.allen SSC Guru Points: 76948 More actions · Answer 3

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

Geoff A SSChampion Points: 11511 More actions · Answer 4

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?

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 5

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 🙁

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

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"

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Geoff A SSChampion Points: 11511 More actions · Answer 7

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

set @SQL = @SQL + '

,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

set @SQL = @SQL + '

FROM cteRN

GROUP BY ID'

exec(@SQL)

drop table #mytable