Change how result is displayed

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)

Change how result is displayed

Author

Topic

craiglkb
Starting Member

2 Posts

Posted - 2005-08-31 : 05:09:42

Hi All

First time poster long time lurker.

Question

I have a table structure that I am outputting from SQL in this format

PRODUCT Siz1 Siz2 Siz3
Test 1 2 3

And I want to output it as

PRODUCT QTY
TEST Siz1 1
TEST Siz2 2
TEST Siz3 3

Any ideas as to how I can go about that?

Thanks in advance.

Craig

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 05:14:11

Hi craiglkb, Welcome to SQL Team Posters!

Just one row to "transpose"?

Or do you want to SUM/Total up all the rows - i.e. make a "crosstab"?

Kristen

craiglkb
Starting Member

2 Posts

Posted - 2005-08-31 : 06:05:10

Hi Kristen, thanks for the Welcome

The table I want to transpose is about 2000 seperate lines - this number will vary over time too.

Thanks

Craig

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 06:35:45

Something like this maybe?


SELECT PRODUCT, 'Siz1', Siz1 AS [Qty]
FROM MyTable
UNION ALL
SELECT PRODUCT, 'Siz2', Siz2 AS [Qty]
FROM MyTable
SELECT PRODUCT, 'Siz3', Siz3 AS [Qty]
FROM MyTable

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 06:58:33

I think Using Union will display the records in proper order
Suppose if the table has following records

PRODUCT Siz1 Siz2 Siz3
Test 1 2 3
Testing 4 5 6

Union All will display

Test s1 1
Testing s1 4
Test s2 2
Testing s2 5
Test s3 3
Testing s3 6

whereas Union will display
Test s1 1
Test s2 2
Test s3 3
Testing s1 4
Testing s2 5
Testing s3 6

Am I correct?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 08:36:34

Yup, I reckon you're right.

UNION will remove duplicates - I assumed that wasn't desireable! (and if there are NO duplicates possible then UNION ALL is better because it avoids the Sort which SQL Server will do to enable it to try to find the non-existent Dups!)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 08:41:53

Thanks Kristen
If Display order is not a problem then yours is best

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 08:47:34

"Display order"

For any repeatable display order it needs an ORDER BY clause, of course

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources