Dynamic column names - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Dynamic column names

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-03 : 12:20:19

Hi

I will first post the sample data as verbally it is a bit diffcult for me to explain the resultset I want. Here is the sample data:

CREATE TABLE TESTING(
id int IDENTITY(1,1),
[1] VARCHAR(50) NULL,
[2] VARCHAR(50) NULL,
[3] VARCHAR(50) NULL,
[4] VARCHAR(50) NULL
)

INSERT INTO TESTING
SELECT NULL,'A2','S3','S4'
UNION
SELECT 'A','S2','S3','S4'
UNION
SELECT 'B','X2','X3','X4'
UNION
SELECT 'C','Y2','Y3','Y4'
UNION
SELECT 'D','Z2','Z3','Z4'

SELECT * FROM TESTING

This is the table data as created above:

ID 1 2 3 4
1 null A2 B3 C4
2 A S2 S3 S4
3 B X2 null null
4 C Y2 Y3 Y4
5 D Z2 Z3 null

And this should be the final output:

COL1 COL2 COL3 COL4
A2 S2 X2 B
A2 S2 Y2 C
A2 S2 Z2 D
B3 S3 Y3 C
B3 S3 Z3 D
C4 S4 Y4 C

And now the problem I am facing. This table has numbered columns 1,2,3...and so on and number of columns is not fixed, it could increase or decrease any time. This table is the result of a DTS, pulled from a file. The DTS cannot be changed and the DB is Sql 2k.

This is one query I could think of:
SELECT (SELECT TOP 1 COL1 FROM T1 WHERE ID<=2) AS A,
(SELECT TOP 1 COL2 FROM T1 WHERE ID<=2 ORDER BY ID DESC) AS B,
COL1, COL2 FROM T1 WHERE COL2 IS NOT NULL
AND ID>2
UNION
SELECT (SELECT TOP 1 COL3 FROM T1 WHERE ID<=2) AS A,
(SELECT TOP 1 COL3 FROM T1 WHERE ID<=2 ORDER BY ID DESC) AS B,
COL1, COL3 FROM T1 WHERE COL3 IS NOT NULL
AND ID>2
.......and so on

In the above I have assumed COL2,COL3 to be as column names 1,2 in the table. So even if I know the column names maybe I could build this query looping the information_schema.columns table but then again the problem is it will be dynamic as I would not know the number of columns. Thus there are 2 problems, one the column name and second the no. of those columns.

If possible I do not want any loops (would be great if avoidable) but more importantly I do not want any dynamic code.

Can anyone please offer a solution to this problem, without any dynamic query. Do you think it is possible.

Thanks

--------------------
Rock n Roll with SQL

hadi teo
Starting Member

4 Posts

Posted - 2009-09-16 : 18:54:07

Hi,

I am not sure whether this approach is the correct one. Alternatively, you can use the "temporary table" concept. Google for the keyword "temporary table sql server". By using the temporary table, you can build the columns definition dynamically. I am not sure about the performance impact.

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-17 : 13:42:09

Thanks for the reply. Yes I know I can do this by temp tables but then the query would become dynamic. Given this problem, is there any way to avoid dynamic sql.

--------------------
Rock n Roll with SQL

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-17 : 13:44:07

quote:
Originally posted by rocknpop

Thanks for the reply. Yes I know I can do this by temp tables but then the query would become dynamic. Given this problem, is there any way to avoid dynamic sql.

Given the earlier statement:

quote:
This table has numbered columns 1,2,3...and so on and number of columns is not fixed, it could increase or decrease any time.

I'd say no.

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-17 : 14:05:23

Thanks robvolk

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources