473,583 Members | 3,346 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Combine multiple rows into 1

aas4mis
97 New Member
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
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.
Jul 1 '10 #1
10 2390
gpl
152 New Member
@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 Recognized Expert Specialist
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 New Member
@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 New Member
@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 Recognized Expert Specialist
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
Jul 1 '10 #6
aas4mis
97 New Member
@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 Recognized Expert Specialist
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 New Member
@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 Recognized Expert Specialist
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

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

Similar topics

1
9717
by: Valerie | last post by:
Hi everyone, I really appreciate if anyone could help me with this tricky problem that I'm having. I'm looking for a sample script to combine data in multiple rows into one row. I'm using sqlserver. This is how data is stored in the table. ID Color 111 Blue 111 Yellow
5
38186
by: Antanas | last post by:
Is it possible to combine multiple rows returned from select statement into one row? SELECT NAME FROM TABLE1; I want all names to be combined into one row seperated by commas.
4
3677
by: musicloverlch | last post by:
I have a table with multiple records in it. I am being forced to combine multiple records into one record in order to be uploaded to a website, and I only get one record per client. How can I systematically combine multiple records into 1? The table looks like this Client ID Service Status 1 Doc Pending...
4
46627
by: Iprogramforlawyers | last post by:
I need to take any amount of rows and turn them into one single row. I'm exporting some data from sql. The program its going into only has one note field. I've used UNION and those types of functions, not sure what to do this time. This is SQL server and will be part of a stored procedure. example data: NOTES NOTE...
7
43128
by: Mintyman | last post by:
Hi, I'm working on a system migration and I need to combine data from multiple rows (with the same ID) into one comma separated string. This is how the data is at the moment: Company_ID Material 0x00C00000000053B86 Lead 0x00C00000000053B86 Sulphur 0x00C00000000053B86 Concrete
18
3980
by: Apple001 | last post by:
Hi all! I am having trouble with joining multiple rows into one row. I will appreciate any help. For columns in the query, I have: invID(autot number), entryDate, invDate, vendor, invoiceAmount from table named tblInvoice, and building, account, percent (allocation in percentage for each building) from table named tblAllocation. This is...
8
22796
by: Mike Reid | last post by:
I have designed a Database that will import data and assign values for ordering material. However I am having problems as I currently have to drop this data into an Excel sheet and apply a Pivot Table to it to manipulate the results. I believe that there is a way to do this in Access, however I have been unsuccessful. Hopefully someone out here...
2
2910
by: kagweh | last post by:
Am new to Access so go easy Each serial number has several comments made for it and what I need is to combine all comments for each serial into one row separated by a comma or l. Now here are tables: dbo_tracking for the serial numbers dbo_comments for the comments Right now this is what I can display
2
5327
by: Michael | last post by:
It seems that a gridview allows us to delete only a single row at a time. How to extend this functionality to select multiple rows and delete all of the selected rows in a single stroke? just like what hotmail web UI is doing now (having the option of selecting multiple rows (using the checkbox provided) and perform a set of operations on them)
3
5635
by: klbachrodt | last post by:
Hi all - I've been browsing this forum for a while now, looking for help, and finally decided to join and ask my question, since I'm not terribly good at writing SQL queries in Access. Here is my dilemma: Table U contains 1 field (Field F, primary key = no dupes) Table N contains 50 fields (including Field F, same as in Table U) * Field F...
0
7821
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8172
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8317
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7928
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6574
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5369
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2326
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1422
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1151
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.