Using table content as rows for SELECT query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using table content as rows for SELECT query

Author

Topic

lindner
Starting Member

3 Posts

Posted - 2010-08-04 : 09:16:52

Hey guys,

I have got a question that might sound a bit difficult.

For setup, I have got the following tables

Table: milestones
-- milestone --
- M1
- M2
- M3
- M4

Table: data

--- ID --- milestone --- date_1 --- date_2 ---
1 M1 date_1_1 date_2_1
2 M1 date_1_2 date_2_2
3 M1 date_1_3 date_2_3
4 M2 date_1_1 date_2_1
5 M2 date_1_2 date_2_2
6 M2 date_1_3 date_2_3
7 M3 date_1_1 date_2_1
8 M3 date_1_2 date_2_2
9 M3 date_1_3 date_2_3
...

In the end, I need a resultset that looks like this..

---------|------ M1 ------|------ M2 ------|------ M3 ------|------ M4 ------| ----- ...
date_1_1 | date_2_1_of_M1 | date_2_1_of_M2 | date_2_1_of_M3 | date_2_1_of_M4 |
---------|----------------|----------------|----------------|----------------|-------
date_1_2 | date_2_2_of_M1 | date_2_2_of_M2 | date_2_2_of_M3 | date_2_2_of_M4 |
---------|----------------|----------------|----------------|----------------|-------
date_1_3 | date_2_3_of_M1 | date_2_3_of_M2 | date_2_3_of_M3 | date_2_3_of_M4 |
---------|----------------|----------------|----------------|----------------|-------
....

Does anybody can help me out with this? I could probably do this with the programming language logic, but I was wondering if I could set this up on the DB Layer?!

thx in advance,
greets

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 09:19:24

Is there a fixed limit on number of milestones?
If there is, try pivot query, otherwise you have to use dynamic sql.

N 56°04'39.26"
E 12°55'05.63"

lindner
Starting Member

3 Posts

Posted - 2010-08-04 : 09:30:35

Well the milestones are fixed, they may change from time to time. They are limited by the content of the milestones-table!!
Do you have an idea how that would work for my example? or atleast, do you have any good resources on pivot queries or dynamic sql? I will search for it as well. thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 09:54:06

See http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources