SQL server query
-
id_no first_name last_name 1 X ABC 2 Y ABC 3 M pqr 4 N PQR Here is the query I am running : SELECT STUFF((select stuff1 from(SELECT distinct T1.id_no 'sequenceno', ('/' + Col2) + ',' + STUFF((SELECT STUFF2 FROM(SELECT distinct T2.id_no 'sequenceno' ,'-' + substring(first_name, 1, 1)'STUFF2' FROM Table2 AS T2 WHERE T2.last_name = T1.last_name AND T2.id_no = 1234 AND T2.id_no = T1.id_no) dt2 order BY dt2.sequenceno FOR XML PATH(''),TYPE ).value('.', 'varchar(MAX)'), 1, 1, '') 'stuff1' FROM Table1 AS T1) dt order BY dt.sequenceno FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Result OUTPUT - ABc,X-Y/ABc,X-Y/pqr,M-N/PQR,M-N Desired OUTPUT - ABc,X-Y/pqr,M-N Can you pls help me on this ?
-
id_no first_name last_name 1 X ABC 2 Y ABC 3 M pqr 4 N PQR Here is the query I am running : SELECT STUFF((select stuff1 from(SELECT distinct T1.id_no 'sequenceno', ('/' + Col2) + ',' + STUFF((SELECT STUFF2 FROM(SELECT distinct T2.id_no 'sequenceno' ,'-' + substring(first_name, 1, 1)'STUFF2' FROM Table2 AS T2 WHERE T2.last_name = T1.last_name AND T2.id_no = 1234 AND T2.id_no = T1.id_no) dt2 order BY dt2.sequenceno FOR XML PATH(''),TYPE ).value('.', 'varchar(MAX)'), 1, 1, '') 'stuff1' FROM Table1 AS T1) dt order BY dt.sequenceno FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Result OUTPUT - ABc,X-Y/ABc,X-Y/pqr,M-N/PQR,M-N Desired OUTPUT - ABc,X-Y/pqr,M-N Can you pls help me on this ?
This is not a good question. The SQL code is mostly unreadable and there is no description of what the desired output should be. The example desired output does not describe the format well enough.
The difficult we do right away... ...the impossible takes slightly longer.
-
id_no first_name last_name 1 X ABC 2 Y ABC 3 M pqr 4 N PQR Here is the query I am running : SELECT STUFF((select stuff1 from(SELECT distinct T1.id_no 'sequenceno', ('/' + Col2) + ',' + STUFF((SELECT STUFF2 FROM(SELECT distinct T2.id_no 'sequenceno' ,'-' + substring(first_name, 1, 1)'STUFF2' FROM Table2 AS T2 WHERE T2.last_name = T1.last_name AND T2.id_no = 1234 AND T2.id_no = T1.id_no) dt2 order BY dt2.sequenceno FOR XML PATH(''),TYPE ).value('.', 'varchar(MAX)'), 1, 1, '') 'stuff1' FROM Table1 AS T1) dt order BY dt.sequenceno FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Result OUTPUT - ABc,X-Y/ABc,X-Y/pqr,M-N/PQR,M-N Desired OUTPUT - ABc,X-Y/pqr,M-N Can you pls help me on this ?
Try this
DECLARE @T TABLE(ID_NO INT IDENTITY,FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50))
INSERT INTO @T VALUES('X','ABC'),('Y','ABC'),('M','PQR'),('N','PQR')
--SELECT * FROM @T
;WITH CTE AS(
SELECT
Rn = ROW_NUMBER() OVER(PARTITION BY t2.LAST_NAME ORDER BY (SELECT 1))
,t2.LAST_NAME
+ ',' +
STUFF(( SELECT '-' + t1.FIRST_NAME
FROM @T t1
WHERE t1.LAST_NAME = t2.LAST_NAME
FOR XML PATH(''),TYPE) .value('.','NVARCHAR(MAX)'),1,1,'')AS Concat_FirstName
FROM @T t2
)
SELECT
STUFF(( SELECT '/' + c1.Concat_FirstName
FROM CTE c1
WHERE c1.Rn = c2.Rn
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,1,'') AS Concat_FirstName
FROM CTE c2 WHERE Rn= 1
GROUP BY c2.RnResult Concat_FirstName ABC,X-Y/PQR,M-N Hope this helps
