My SQL table is like following
City_Code Post_Code Post_Code_Description
100 A1 ABC
100 C8 XYZ
100 Z3 MNO
200 D4 LMN
300 E3 IJK
300 B9 RST
It's a mapping between city_code and Post_Code. One City_Code has many Post Codes. Now i want to run a Query to get something like following
City_Code Post_Code Post_Code_Description
100 A1,C8,Z3 ABC,XYZ,MNO
200 D4 LMN
300 E3,B9 IJK,RST
Can you please help me with thisy SQL table is like following
try this:
SELECT City_Code,
Post_Code =
STUFF((SELECT ', ' + Post_Code
FROM your_table b
WHERE b.City_Code = a.City_Code
FOR XML PATH('')), 1, 2, ''),
Post_Code_Description=
STUFF((SELECT ', ' + Post_Code_Description
FROM your_table b
WHERE b.City_Code = a.City_Code
FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY City_Code
If you are using MySQL you can use GROUP_CONCAT()
select City_Code,
GROUP_CONCAT(Post_Code) Post_Code,
GROUP_CONCAT(Post_Code_Description) post_code_description
from yourtable
group by City_Code
For SQL Server you can use STUFF() and FOR XML PATH()
select city_code,
Stuff((SELECT ', ' + post_code
FROM yourtable t2
where t1.city_code = t2.city_code
FOR XML path('')),1,1,'') Post_Code,
Stuff((SELECT ', ' + post_code_description
FROM yourtable t2
where t1.city_code = t2.city_code
FOR XML path('')),1,1,'') post_code_description
from yourtable t1
group by city_code
try this:
select city_code,substring((select ',' + post_code
from city b where a.city_code=b.city_code
for xml path('')
),2,100)as post_code,
substring((select ',' + post_code_description
from city c where a.city_code=c.city_code
for xml path('')
),2,100)as post_code_description
from city a
group by a.city_code
Use a recursive query for this:
--Prepare Dummy Data
;WITH CITIES
AS (SELECT 100 AS City_Code,
'A1' AS Post_code,
'ABC' AS Post_Code_Description
UNION
SELECT 100 AS City_Code,
'C8' AS Post_code,
'XYZ' AS Post_Code_Description
UNION
SELECT 100 AS City_Code,
'Z3' AS Post_code,
'MNO' AS Post_Code_Description
UNION
SELECT 200 AS City_Code,
'D4' AS Post_code,
'LMN' AS Post_Code_Description
UNION
SELECT 300 AS City_Code,
'E3' AS Post_code,
'IJK' AS Post_Code_Description
UNION
SELECT 300 AS City_Code,
'B9' AS Post_code,
'RST' AS Post_Code_Description),
--Add Row numbers to each row
PREPARE
AS (SELECT *,
ROW_NUMBER ()
OVER (
PARTITION BY CITY_CODE
ORDER BY CITY_CODE) RN
FROM CITIES),
--Start Recursive CTE
RECURSIVE
AS (
--Anchor Query
SELECT CITY_CODE,
CAST(POST_CODE AS VARCHAR(MAX)) Post_code,
CAST(POST_CODE_DESCRIPTION AS VARCHAR(MAX))
Post_Code_Description,
1 AS LEVEL,
RN
FROM PREPARE
WHERE RN = 1
UNION ALL
--Recursive Query
SELECT T1.CITY_CODE,
T1.POST_CODE + ',' + T2.POST_CODE,
T1.POST_CODE_DESCRIPTION + ','
+ T2.POST_CODE_DESCRIPTION,
T2.LEVEL + 1,
T1.RN
FROM PREPARE AS T1
INNER JOIN RECURSIVE AS T2
ON T1.RN = T2.RN + 1
AND T1.CITY_CODE = T2.CITY_CODE)
--Final Results
SELECT T1.CITY_CODE,
T1.POST_CODE,
T1.POST_CODE_DESCRIPTION
FROM RECURSIVE T1
INNER JOIN (SELECT CITY_CODE,
COUNT(*) cnt
FROM CITIES
GROUP BY CITY_CODE)T2
ON T1.CITY_CODE = T2.CITY_CODE
WHERE T1.LEVEL = T2.CNT
