help with simple query - 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 2005 Forums

Transact-SQL (2005)

help with simple query

Author

Topic

kowalsky
Starting Member

29 Posts

Posted - 2010-07-08 : 13:16:46

hi all,
i have a simple problem:


ID   TBL   COL   VAL
1    AAA   COL1  100
2    AAA   COL2  XYZ
3    AAA   COL3  200
4    AAA   COL1   50
5    AAA   COL2  XYZ
6    AAA   COL3  100

What would be the query that displays the following:


ID   TBL   COL   VAL1   VAL2
1    AAA   COL1  100    50
2    AAA   COL2  XYZ    XYZ
3    AAA   COL3  200    100

I tried:
select t1.id, t1.tbl, t1.col, t1.val as VAL1 , t2.val as VAL2
from myTable t1, myTable t2
where t1.TBL=t2.TBL and
t1.COL=t2.COL
but I am getting nowhere.

Thanks,

kowalsky

kowalsky
Starting Member

29 Posts

Posted - 2010-07-08 : 13:25:50

hi all,
false alarm!
i guess I would have to add t1.id < t2.id and everything would be just fine,
thanks,
kowalsky

kowalsky

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-09 : 06:45:37

or rather than use some joining method

http://www.w3schools.com/sql/sql_join.asp

it might help you...

A maze make you much more better

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-07-09 : 11:09:56

How can the Val2 Value come for 1,2,3 when there ID is 4,5,6?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-07-10 : 02:25:34

[code]
SELECT ID = ROW_NUMBER() OVER(ORDER BY COL ASC),
TBL,
COL,
VAL1 = MAX(CASE WHEN rec_id = 1 THEN VAL END),
VAL2 = MAX(CASE WHEN rec_id = 2 THEN VAL ELSE '' END)
FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY COL ORDER BY ID) AS Rec_id
FROM MyTable
) AS D
GROUP BY COL, TBL
ORDER BY ID ;
[/code]

______________________

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-11 : 21:54:58

[code]
; with data
as
(
select col_no = row_number() over (partition by TBL, COL order by VAL),
TBL, COL, VAL
from yourtable
)
select TBL, COL, VAL1 = [1], VAL2 = [2]
from data d
pivot
(
max(VAL)
for col_no in ([1], [2])
) p
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources