469,138 Members | 1,393 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Combine multiple rows into 1

aas4mis
97
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT devId,
  2. (CASE WHEN daName = 'userAttribute.assetname' THEN daValue END) AS assetname,
  3. (CASE WHEN daName = 'userAttribute.adapter.pccardx5cphoton1.ipaddr' THEN daValue END) AS ipaddr
  4. FROM tDeviceAttributes
  5. WHERE daName='userAttribute.assetname' or daName='userAttribute.adapter.pccardx5cphoton1.ipaddr'
  6. GROUP BY devId, daName, daValue
  7. ORDER BY devID
  8.  
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.
Jul 1 '10 #1
10 2073
gpl
152 100+
@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
Jul 1 '10 #2
ck9663
2,878 Expert 2GB
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
Jul 1 '10 #3
aas4mis
97
@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???
Jul 1 '10 #4
aas4mis
97
@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.
Jul 1 '10 #5
ck9663
2,878 Expert 2GB
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
Jul 1 '10 #6
aas4mis
97
@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.
Jul 1 '10 #7
ck9663
2,878 Expert 2GB
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
Jul 1 '10 #8
aas4mis
97
@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.
Jul 1 '10 #9
ck9663
2,878 Expert 2GB
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
Jul 1 '10 #10
aas4mis
97
Thanks for the suggestion CK, but I believe I have this one whipped.

Expand|Select|Wrap|Line Numbers
  1. SELECT t1.devId, t1.daValue as assetname, t2.daValue as ipaddr
  2. FROM tDeviceAttributes t1
  3. INNER JOIN tDeviceAttributes t2 on t1.devId = t2.devId
  4. WHERE t1.daName='userAttribute.assetname' and t2.daName='userAttribute.adapter.pccardx5cphoton1.ipaddr'
  5. ORDER BY assetname
  6.  
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.
Jul 1 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Antanas | last post: by
4 posts views Thread by musicloverlch | last post: by
2 posts views Thread by Michael | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.