combine many records into one and one record into many

combine many records into one and one record into many

Post by yourfrie » Fri, 23 Nov 2001 03:46:25



I am new to SQL server and I would like to like is there any way I can
combine many records into one and one record into many.

Input/Output (many records):

id  DX
1   A
1   B
1   C
2   A
2   B

Output/Input (one record):

id  DX1  DX2  DX3
1   A    B    C
2   A    B

I am a SAS programmer. I can do it pretty easily in SAS but I don't
know how to do it in Query Analyser/DTS scripts or whatever. Any help
is welcome.

 
 
 

combine many records into one and one record into many

Post by --CELKO » Fri, 23 Nov 2001 13:58:51


Quote:>> .. is there any way I can combine many records into one and one

record into many. <<

You need to know the maximum number of columns in advance.

CREATE TABLE Foobar
(id INTEGER NOT NULL,
 dx CHAR(1) NOT NULL,
 PRIMARY KEY (id, dx));

CREATE TABLE Barfoor
(id INTEGER NOT NULL,
 dx1 CHAR(1) NOT NULL,
 dx2 CHAR(1) NOT NULL,
 dx3 CHAR(1) NOT NULL,
 PRIMARY KEY (id, dx1, dx2, dx3));

INSERT INTO Barfoo (id, dx1, dex2, dx3)
SELECT id,
       MAX(CASE WHEN dx = 'A' THEN 'A' ELSE NULL END,
       MAX(CASE WHEN dx = 'B' THEN 'B' ELSE NULL END,
       MAX(CASE WHEN dx = 'C' THEN 'C' ELSE NULL END
  FROM Foobar
 GROUP BY id;

Conversely,

INSERT INTO Foobar (id, dx)
SELECT B1.id, B1.dx1
  FROM Barfoo AS B1
UNION ALL
SELECT B2.id, B2.dx2
  FROM Barfoo AS B2
UNION ALL
SELECT B3.id, B3.dx3
  FROM Barfoo AS B3;  

But do not use SQL for a report writer and do not denormalize your
tables like this.

 
 
 

1. Combining multiple records of same types into one record

I have a database of products owned by customers the problem is each product
own has it1s own record. Example:

Company A might own 6 different products and hence they are listed as 6
different records.

How can I combine all records into one so that company A is listed once in
the database yet all the product they own are included in that record ?

Any help greatly appreciate.

If I am too confusing sorry, email me and I1ll try to explain it better

MC

2. index files of database

3. One record to one or many records

4. Data form wizzard

5. Combine two records into one

6. Help with Database Recovery

7. Combining multiple records into one coma-separated field

8. Time Warner or AOL busting your balls? BUST BACK! Message the FCC with your comment! LINK to FCC HERE! 6652

9. combining two record fields into one combo box

10. Question: Combining records to one 'object'

11. How to combine multiple records into one beyond a simple union query

12. Filtering one record per master in a one to many join

13. SQL query - Get one record if it exists else get another one