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

Try this:

;WITH CTE_ID_Name AS (
    SELECT DISTINCT id, name 
    FROM #Tempaddress
),
CTE_Addr1 AS (
    SELECT DISTINCT id, name, Addr1 
    FROM #Tempaddress
    WHERE Addr1 IS NOT NULL
),
CTE_Addr2 AS (
    SELECT DISTINCT id, name, Addr2 
    FROM #Tempaddress
    WHERE Addr2 IS NOT NULL
),
CTE_State AS (
    SELECT DISTINCT id, name, state 
    FROM #Tempaddress
    WHERE state IS NOT NULL
),
CTE_Zip AS (
    SELECT DISTINCT id, name, zip 
    FROM #Tempaddress
    WHERE zip IS NOT NULL
)

SELECT c1.id, c1.name, c2.Addr1, c3.Addr2, c4.state, c5.zip 
FROM CTE_ID_Name AS c1
LEFT JOIN CTE_Addr1 AS c2 ON c1.id = c2.id
LEFT JOIN CTE_Addr2 AS c3 ON c1.id = c3.id
LEFT JOIN CTE_State AS c4 ON c1.id = c4.id
LEFT JOIN CTE_Zip AS c5 ON c1.id = c5.id;

Answer 3

Hi @Nethan ,

Here is a solution based on the STRING_AGG() function. It requires SQL Server 2017 and later.

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  
  
SELECT * FROM @tbl;  
  
;WITH rs AS  
(  
 SELECT id, name  
 , STRING_AGG(CONVERT(NVARCHAR(max), Addr1), ',') AS Addr1List  
 , STRING_AGG(CONVERT(NVARCHAR(max), Addr2), ',') AS Addr2List  
 , STRING_AGG(CONVERT(NVARCHAR(max), state), ',') AS stateList  
 , STRING_AGG(CONVERT(NVARCHAR(max), zip), ',') AS zipList  
 FROM @tbl  
 GROUP BY id, name  
)  
SELECT ID, name  
 , LEFT(Addr1List, IIF(CHARINDEX(',', Addr1List) > 0, CHARINDEX(',', Addr1List) - 1,LEN(Addr1List))) AS Addr1  
 , LEFT(Addr2List, IIF(CHARINDEX(',', Addr2List) > 0, CHARINDEX(',', Addr2List) - 1,LEN(Addr2List))) AS Addr2  
 , LEFT(stateList, IIF(CHARINDEX(',', stateList) > 0, CHARINDEX(',', stateList) - 1,LEN(stateList))) AS state  
 , LEFT(zipList, IIF(CHARINDEX(',', zipList) > 0, CHARINDEX(',', zipList) - 1,LEN(zipList))) 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  |  
+----+------+-------+-------+-------+-------+  

Answer 4

Hi @Nethan ,

Another way is to use the COALESCE function(COALESCE (Transact-SQL))：

;with cte   
as(select *,row_number() over(partition by id order by id)rn from #Tempaddress)  
,cte2 as  
(select c1.id,c1.name,  
COALESCE(c1.Addr1,c2.Addr1,c3.Addr1) Addr1,  
COALESCE(c1.Addr2,c2.Addr2,c3.Addr2) Addr2,  
COALESCE(c1.state,c2.state,c3.state ) state,  
COALESCE(c1.zip,c2.zip,c3.zip) zip,c1.rn  
from cte c1  
left join cte c2 on c1.rn=c2.rn-1 and c1.id=c2.id   
left join cte c3 on c2.rn=c3.rn-1 and c2.id=c3.id)  
  
select id,name,Addr1,Addr2,state,zip from cte2 where rn<2  

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi @Nethan ,

Please refer to:

    ;with cte   
    as(select *,row_number() over(order by id)rn from #Tempaddress)  
    ,cte2 as  
    (select c1.id,c1.name,  
    case when c1.Addr1 is null and c2.Addr1 is null then c3.Addr1 else isnull(c1.Addr1,c2.Addr1) end Addr1,  
    case when c1.Addr2 is null and c2.Addr2 is null then c3.Addr2 else isnull(c1.Addr2,c2.Addr2) end Addr2,  
    case when c1.state is null and c2.state is null then c3.state else isnull(c1.state,c2.state) end state,  
    case when c1.zip is null and c2.zip is null then c3.zip else isnull(c1.zip,c2.zip) end zip,  
    row_number() over(partition by c1.id order by c1.id) rr  
    from cte c1  
    left join cte c2 on c1.rn=c2.rn-1   
    left join cte c3 on c2.rn=c3.rn-1)  
      
    select id,name,Addr1,Addr2,state,zip from cte2 where rr<2  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.