Combine multiple rows into 1

**gpl** · Jul 1 '10, 04:55 PM

Originally posted by aas4mis

I have a table with a deviceid, attribute type, and attribute value. There are many attributes for a single device id. I want my query to retrieve a single device id with multiple attribute values. Here's what I have so far.
[code=sql]
SELECT devId,
(CASE WHEN daName = 'userAttribute. assetname' THEN daValue END) AS assetname,
(CASE WHEN daName = 'userAttribute. adapter.pccardx 5cphoton1.ipadd r' THEN daValue END) AS ipaddr
FROM tDeviceAttribut es
WHERE daName='userAtt ribute.assetnam e' or daName='userAtt ribute.adapter. pccardx5cphoton 1.ipaddr'
GROUP BY devId, daName, daValue
ORDER BY devID
[/code]
This gives me a row for each attribute value.. not good.

current output:
devid assetname ipaddr
0006a7019547b6f 138000050bf7a60 e2 NULL 172.23.152.201
0006a7019547b6f 138000050bf7a60 e2 104178 NULL
0046ae019447db0 1a8000050bf7a60 e2 104115 NULL
0146ae01934726f 178000050bf7a60 e2 NULL 172.23.152.207
0146ae01934726f 178000050bf7a60 e2 104108 NULL
0146ae01934788e 178000050bf7a60 e2 NULL 172.23.152.191

needs to be:
devId assetname ipaddr
0006a7019547b6f 138000050bf7a60 e2 104178 172.23.152.201
0046ae019447db0 1a8000050bf7a60 e2 104115 172.23.152.207
0146ae01934726f 178000050bf7a60 e2 104108 172.23.152.191

Help would be appreciated.

This is very nearly impossible, it would be ugly and slow

Could you normalise your database, it will be a lot easier on you

**ck9663** · Jul 1 '10, 04:58 PM

Here, read this. As you can see, you're going to need a key that can be used for sorting. But you should have some idea on how it's done.

Happy Coding!!!

~~ CK

**aas4mis** · Jul 1 '10, 05:00 PM

Originally posted by gpl

This is very nearly impossible, it would be ugly and slow

Could you normalise your database, it will be a lot easier on you

No, unable to normalize. This is not my DB, actually designed by motorola (and works for their purposes). I'm making a custom web page to interact with the database and need this query for a stored procedure. I don't see where this would be slow as there are less than 7,000 total records in this table. Surely there must be a clean way to write this. ... is there are Shirley in the building???

**aas4mis** · Jul 1 '10, 05:08 PM

Originally posted by ck9663

Here, read this. As you can see, you're going to need a key that can be used for sorting. But you should have some idea on how it's done.

Happy Coding!!!

~~ CK

Thanks ck9663, but I don't think this is the correct route. The suggested query relies on the previous row, regardless of identifier (in my case devId). Should a device be missing an attribute the next device will receive the wrong value. I'm thinking this would best be solved with a self join, but not sure how to implement it.

**ck9663** · Jul 1 '10, 05:12 PM

Why did you join this two rows:

0046ae019447db0 1a8000050bf7a60 e2 104115 NULL
0146ae01934726f 178000050bf7a60 e2 NULL 172.23.152.207

Into one row:
0046ae019447db0 1a8000050bf7a60 e2 104115 172.23.152.207

What's your logic in consolidating these rows?

~~ CK

**aas4mis** · Jul 1 '10, 05:21 PM

Originally posted by ck9663

Why did you join this two rows:

0046ae019447db0 1a8000050bf7a60 e2 104115 NULL
0146ae01934726f 178000050bf7a60 e2 NULL 172.23.152.207

Into one row:
0046ae019447db0 1a8000050bf7a60 e2 104115 172.23.152.207

What's your logic in consolidating these rows?

~~ CK

Oops, my mistake. Typo, I just copy/pasted the "needs to be" table, only the first row is correct. But this illustrates a good point, the suggested query would've done the same thing, since it's ordered by devId but apparently a couple attributes are MIA.

**ck9663** · Jul 1 '10, 05:26 PM

Yes. As I mentioned, you're going to need something to sort your table with. If you just want it to run through the table picking the value from the next row, use ROW_NUMBER() to have a sequential record/row number that you can use.

Good Luck!!!

~~ CK

**aas4mis** · Jul 1 '10, 05:40 PM

Originally posted by ck9663

Yes. As I mentioned, you're going to need something to sort your table with. If you just want it to run through the table picking the value from the next row, use ROW_NUMBER() to have a sequential record/row number that you can use.

Good Luck!!!

~~ CK

There's still the issue of missing attributes. Say devid 1 has ipaddr and assetname, devid 2 has assetname, devid3 has assetname.. won't devid 2 and devid 3 now have devid 1's ipaddr? sorted by devid that is.

**ck9663** · Jul 1 '10, 05:48 PM

Use the PARTIOTION BY clause of the ROW_NUMBER() to restart numbering. It means you'll have a sequence of number per DEVID. If the DEVID change, it'll restart the numbering to 1...and so on...

Good Luck!!!

~~ CK

**aas4mis** · Jul 1 '10, 06:03 PM

Thanks for the suggestion CK, but I believe I have this one whipped.

[code=sql]
SELECT t1.devId, t1.daValue as assetname, t2.daValue as ipaddr
FROM tDeviceAttribut es t1
INNER JOIN tDeviceAttribut es t2 on t1.devId = t2.devId
WHERE t1.daName='user Attribute.asset name' and t2.daName='user Attribute.adapt er.pccardx5cpho ton1.ipaddr'
ORDER BY assetname
[/code]

results:
devid assetname ipaddr
0992a301b1474be 178000050bf7a60 e2 103189 172.23.223.87
1b26a701944709d 118000050bf7a60 e2 104102 172.23.152.212
19039601854796e 128000050bf7a60 e2 104103 172.23.152.206
1d46ae019447fc9 1b8000050bf7a60 e2 104104 172.23.152.228
0246b301824755c 1e8000050bf7a60 e2 104105 172.23.152.223
2346ae019347d6f 178000050bf7a60 e2 104106 172.23.152.252
0a86a00198474ee 1c8000050bf7a60 e2 104107 172.23.152.210
0146ae01934726f 178000050bf7a60 e2 104108 172.23.152.207
0546ae019347b7a 178000050bf7a60 e2 104109 172.23.152.242
1b46ae01944777e 128000050bf7a60 e2 104110 172.23.152.219
1846ae019447741 198000050bf7a60 e2 104111 172.23.152.221
1106a70195477fb 138000050bf7a60 e2 104112 172.23.152.209
0846ae019447d32 1a8000050bf7a60 e2 104113 172.23.152.142
1f46ae019447ae4 198000050bf7a60 e2 104114 172.23.152.216
0746ae01944735e 198000050bf7a60 e2 104116 172.23.152.240

Verified results, Good to go.

Combine multiple rows into 1

Combine multiple rows into 1

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment