retrieving repeated columns (e.g. col1, col2) as multiple rows 1

[DiverMarv]

Hello all,

I have a need to retrieve data from a table that currently stores data as iterative rows (e.g., column1, column2, etc.) I need to retrieve that as a generic set of columns with the repeated column data as rows.

Thanks

 

[bborissov]

Can you post some data and desired result from it?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

You can do this with a union all query. Ex:

Select Id, Column1
From   TableName

Union All

Select Id, Column2
From   TableName

Union All

Select Id, Column3
From   TableName

Order By Id

-George

"the screen with the little boxes in the window." - Moron

 

[SQLSister]

Hopefully you are doing this to fix that bad table design?

Your system would be much improved if you created a normalized table instead.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

One technique I've used is:

SELECT
   CASE Num
      WHEN 1 THEN Col1
      WHEN 2 THEN Col2
      WHEN 3 THEN Col3
      ...
   END
FROM
   YourTable T
   CROSS JOIN (SELECT Num = 1 UNION SELECT 2 UNION SELECT 3 ...) X

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]