sql query help needed

Question

Hi All,

Thank you for helping those who are in need.

I need a help in writing a sql query to get the desired out put shown below,
I have multiple records with same id in a table other columns may vary, we need to process those grouped records and get a single record pick them as shown below. some columns have nulls ignore them if the value exists in other record, pick that value, if no value exists in any of the rows in that group just leave as null

create table #Tempaddress (id int, name varchar(10), Addr1 varchar(50), Addr2 varchar(50), state char(2), zip char(5))

insert into #tempaddress

Values( 1 ,'Tom', null, 'xyz', 'OH',NULL)

,(1, 'Tom', 'zxy', 'xyz', NULL,NULL)

,(1, 'Tom', NULL, NULL, NULL,'28764')

,( 2 ,'Bill', null, 'xyz', 'AZ',NULL)

,(2, 'Bill', 'zxy', 'xyz', NULL,NULL)

,(3, 'Kate', 'zxy', 'xyz', NULL,NULL)

Desired output

1 Tom zxy, xyz, OH,28764

2 Bill zxy, xyz, AZ, NULL

3 Kate, zxy, xyz, NULL,NULL

Answer 1

Also check a method that experiments with pivoting, for up to six values:

;
with Q1 as
(
    select id, name, 
        cast(Addr1 as varchar(max)) as v1,
        cast(Addr2 as varchar(max)) as v2,
        cast(state as varchar(max)) as v3,
        cast(Zip as varchar(max)) as v4
    from #Tempaddress 
),
Q2 as 
(
    select id, name, min(t) as t, val
    from Q1
    unpivot
    (
        val for t in ( v1, v2, v3, v4 )
    ) as u
    group by id, name, val
),
Q3 as
(
    select id, name, val, row_number() over (partition by id, name order by t) as rn
    from Q2
)
select id, name, [1], [2], [3], [4], [5], [6]
from Q3
pivot
(
    max(val) for rn in ( [1], [2], [3], [4], [5], [6] )
) as p
order by id

Dou you want to make a comma-separated text?

Answer 2

Hi @Nethan ,

Here is another method based on XML.
It supports unlimited number of rows per individual.
SQL Server 2005 onwards.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (id int, name varchar(10), Addr1 varchar(50), Addr2 varchar(50), state char(2), zip char(5));  
INSERT INTO @tbl VALUES  
( 1 ,'Tom', null, 'xyz', 'OH',NULL)  
,(1, 'Tom', 'zxy', 'xyz', NULL,NULL)  
,(1, 'Tom', NULL, NULL, NULL,'28764')  
,( 2 ,'Bill', null, 'xyz', 'AZ',NULL)  
,(2, 'Bill', 'zxy', 'xyz', NULL,NULL)  
,(3, 'Kate', 'zxy', 'xyz', NULL,NULL);  
-- DDL and sample data population, end  
  
-- Method by using just XML  
;WITH rs AS  
(  
 SELECT id, name  
 , (SELECT Addr1 AS r  
 FROM @tbl AS O  
 WHERE O.id = C.id   
 FOR XML PATH(''), TYPE, ROOT('root')) AS Addr1XML  
 , (SELECT Addr2 AS r  
 FROM @tbl AS O  
 WHERE O.id = C.id   
 FOR XML PATH(''), TYPE, ROOT('root')) AS Addr2XML  
 , (SELECT state AS r  
 FROM @tbl AS O  
 WHERE O.id = C.id   
 FOR XML PATH(''), TYPE, ROOT('root')) AS stateXML  
 , (SELECT zip AS r  
 FROM @tbl AS O  
 WHERE O.id = C.id   
 FOR XML PATH(''), TYPE, ROOT('root')) AS zipXML  
 FROM @tbl AS c  
 GROUP BY id, name  
)  
SELECT ID, name  
 , Addr1XML.value('(/root/r/text())[1]','VARCHAR(30)') AS Addr1  
 , Addr2XML.value('(/root/r/text())[1]','VARCHAR(30)') AS Addr2  
 , stateXML.value('(/root/r/text())[1]','VARCHAR(30)') AS state  
 , zipXML.value('(/root/r/text())[1]','VARCHAR(30)') AS zip  
FROM rs  
ORDER BY ID;  

Output

+----+------+-------+-------+-------+-------+  
| ID | name | Addr1 | Addr2 | state |  zip  |  
+----+------+-------+-------+-------+-------+  
|  1 | Tom  | zxy   | xyz   | OH    | 28764 |  
|  2 | Bill | zxy   | xyz   | AZ    | NULL  |  
|  3 | Kate | zxy   | xyz   | NULL  | NULL  |  
+----+------+-------+-------+-------+-------+