Combine multiple rows into 1

[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.

SELECT devId,
(CASE WHEN daName = 'userAttribute.assetname' THEN daValue END) AS assetname,
(CASE WHEN daName = 'userAttribute.adapter.pccardx5cphoton1.ipaddr' THEN daValue END) AS ipaddr
FROM tDeviceAttributes
WHERE daName='userAttribute.assetname' or daName='userAttribute.adapter.pccardx5cphoton1.ipaddr'
GROUP BY devId, daName, daValue
ORDER BY devID
 

This gives me a row for each attribute value.. not good.

current output:
devid assetname ipaddr
0006a7019547b6f138000050bf7a60e2 NULL 172.23.152.201
0006a7019547b6f138000050bf7a60e2 104178 NULL
0046ae019447db01a8000050bf7a60e2 104115 NULL
0146ae01934726f178000050bf7a60e2 NULL 172.23.152.207
0146ae01934726f178000050bf7a60e2 104108 NULL
0146ae01934788e178000050bf7a60e2 NULL 172.23.152.191

needs to be:
devId assetname ipaddr
0006a7019547b6f138000050bf7a60e2 104178 172.23.152.201
0046ae019447db01a8000050bf7a60e2 104115 172.23.152.207
0146ae01934726f178000050bf7a60e2 104108 172.23.152.191

Help would be appreciated.

[gpl]

@aas4mis
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]

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]

@gpl
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]

@ck9663
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]

Why did you join this two rows:

0046ae019447db01a8000050bf7a60e2 104115 NULL
0146ae01934726f178000050bf7a60e2 NULL 172.23.152.207

Into one row:
0046ae019447db01a8000050bf7a60e2 104115 172.23.152.207

What's your logic in consolidating these rows?

~~ CK

[aas4mis]

@ck9663
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]

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]

@ck9663
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]

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]

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

SELECT t1.devId, t1.daValue as assetname, t2.daValue as ipaddr
FROM tDeviceAttributes t1
INNER JOIN tDeviceAttributes t2 on t1.devId = t2.devId
WHERE t1.daName='userAttribute.assetname' and t2.daName='userAttribute.adapter.pccardx5cphoton1.ipaddr'
ORDER BY assetname
 

results:
devid assetname ipaddr
0992a301b1474be178000050bf7a60e2 103189 172.23.223.87
1b26a701944709d118000050bf7a60e2 104102 172.23.152.212
19039601854796e128000050bf7a60e2 104103 172.23.152.206
1d46ae019447fc91b8000050bf7a60e2 104104 172.23.152.228
0246b301824755c1e8000050bf7a60e2 104105 172.23.152.223
2346ae019347d6f178000050bf7a60e2 104106 172.23.152.252
0a86a00198474ee1c8000050bf7a60e2 104107 172.23.152.210
0146ae01934726f178000050bf7a60e2 104108 172.23.152.207
0546ae019347b7a178000050bf7a60e2 104109 172.23.152.242
1b46ae01944777e128000050bf7a60e2 104110 172.23.152.219
1846ae019447741198000050bf7a60e2 104111 172.23.152.221
1106a70195477fb138000050bf7a60e2 104112 172.23.152.209
0846ae019447d321a8000050bf7a60e2 104113 172.23.152.142
1f46ae019447ae4198000050bf7a60e2 104114 172.23.152.216
0746ae01944735e198000050bf7a60e2 104116 172.23.152.240

Verified results, Good to go.