How to retrieve records in a single row(help me..)

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

General SQL Server Forums

New to SQL Server Programming

How to retrieve records in a single row(help me..)

Author

Topic

ntdeepa
Starting Member

2 Posts

Posted - 2008-07-02 : 08:30:55

Hi Friends,

I have a table called scale with 2 columns scaleid,scalename

and another table called scalevalue with 4 columns scalevalueid,scaleid,scalevalue,points

the scaleid is a foriegn key derived from scale table.and for every scalename there must be atleast one scalevalue and maximum of 5 records.

records in first table

scaleid scalename
-----------------------------------------
1 Scale1
2 Scale2
3 Scale3

records in second table

sclaevalueid scaleid scalevalue points
------------------------------------------------------------
1 1 Good 100
2 2 Yes 80
3 2 No 20
4 3 Good 50
5 3 Better 30
6 3 Worst 20

i want to retrieve the records combining both the table as

sv - scalevalue
p - points

scaleid scalename sv1 p1 sv2 p2 sv3 p3 sv4 p4 sv5 p5
--------------------------------------------------------------------
1 Scale1 Good 100
2 Scale2 Yes 80 No 20
3 Scale3 Good 50 Better 30 Worst 20

please can anyone help me to retrieve the records in the above format

Thanks,
Hema

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 13:42:00

Are you definite how many values will come for a single scale in second table?

ntdeepa
Starting Member

2 Posts

Posted - 2008-07-02 : 18:17:11

As i posted, there may be 1 or maximum 5 values for a single scale

for ex: one scale may contain 2 scale value, and another scale may contain 3 value but the maximum scale value is 5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-02 : 22:11:14

if you are using SQL Server 2005, you can use the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 02:09:58

you can even do like this if sql 2005:-

SELECT t.scalename,
MAX(CASE WHEN RowNo=1 THEN scalevalue ELSE NULL END) AS sv1,
MAX(CASE WHEN RowNo=1 THEN points ELSE NULL END) AS p1,
MAX(CASE WHEN RowNo=2 THEN scalevalue ELSE NULL END) AS sv2,
MAX(CASE WHEN RowNo=2 THEN points ELSE NULL END) AS p2,
MAX(CASE WHEN RowNo=3 THEN scalevalue ELSE NULL END) AS sv3,
MAX(CASE WHEN RowNo=3 THEN points ELSE NULL END) AS p3,
MAX(CASE WHEN RowNo=4 THEN scalevalue ELSE NULL END) AS sv4,
MAX(CASE WHEN RowNo=4 THEN points ELSE NULL END) AS p4,
MAX(CASE WHEN RowNo=5 THEN scalevalue ELSE NULL END) AS sv5,
MAX(CASE WHEN RowNo=5 THEN points ELSE NULL END) AS p5
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY scaleid ORDER BY sclaevalueid) AS RowNo,
scalename,scalevalue, points
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.scaleid=t1.scaleid
)t
GROUP BY t.scalename

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources