Make a view with Column names from row values

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)

Make a view with Column names from row values

Author

Topic

pmd203
Starting Member

4 Posts

Posted - 2008-05-22 : 09:14:34

Hi,

I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.

At the moment I have something like:

Quantity : Value
--------------------
Quantity 1 : 0.12
Quantity 1 : 0.56
Quantity 2 : 2.36
Quantity 2 : 5.34
Quantity 2 : 4.13
Quantity 3 : 10
Quantity 3 : 15

and I need a view that looks like:

Quantity 1 : Quantity 2 : Quantity 3
-------------------------------------
0.12 : 2.36 : 10
0.56 : 5.34 : 15
null(?) : 4.13 : null(?)

I've tried using pivots but they don't seem to help

Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-22 : 09:32:36

well show us what you tried

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

pmd203
Starting Member

4 Posts

Posted - 2008-05-22 : 09:44:59

It was something like:

SELECT value AS 'Quantity',
[Quantity 1], [Quantity 2], [Quantity 3], [Quantity 4]
FROM
(SELECT Value, Quantity
FROM CiRes) AS SourceTable
PIVOT
(
value
FOR Quantity IN ([Quantity 1], [Quantity 2], [Quantity 3], [Quantity 4])
) AS PivotTable

Have spent most of this morning trying to figure this one out using loads of different ways.... but I think that's how I tried to use the pivot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 11:10:51

[code]SELECT SUM(CASE WHEN Quantity ='Quantity 1' THEN Value ELSE 0 END) AS Quantity1,
SUM(CASE WHEN Quantity ='Quantity 2' THEN Value ELSE 0 END) AS Quantity2,
SUM(CASE WHEN Quantity ='Quantity 3' THEN Value ELSE 0 END) AS Quantity3,
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Quantity ORDER BY Value) AS RowNo,
*
FROM YourTable
)t
GROUP BY t.RowNo[/code]

pmd203
Starting Member

4 Posts

Posted - 2008-05-23 : 08:41:53

ok that seems to work.... but if i want to exclude rows where Quantity 1 = 0 how would i do that? I can't do either

WHERE Quantity1 <> 0

and I can't do

WHERE

SUM(CASE WHEN Quantity ='Quantity 1' THEN Value ELSE 0 END) <> 0

What can i do?!

pmd203
Starting Member

4 Posts

Posted - 2008-05-23 : 08:47:59

But i can do

WHERE

(CASE WHEN Quantity ='Quantity 1' THEN Value ELSE 0 END) <> 0

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-06-03 : 18:37:52

Is there a way to do this dynamically without having to hard code the quantity field.

Reards to all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 00:00:18