April 17, 2017 at 8:32 am
I want to concatenate below table rows using dynamic SQL
empno | jobid | vesselno | portid
1 121 1455 1231
1 122 1486 1322
1 123 1496 1456
output required is
empno | jobid | vesselno | portid
1 | 121,122,123 |1455,1486,1496 | 1231,1322,1456
I tried using stuff command for empno and jobno it is working fine, but i want it for vesselno and portid as well, also same thing i have to complete it using dynamic SQL
Select
distinct A.empno,
--K.jobid,
jobno = STUFF(CAST((
SELECT [text()] = ', ' + ev.jobid
FROM
April 17, 2017 at 10:41 am
You've been around long enough to know that's not the correct way to post data.
You said that this needs to be dynamic, but there is nothing about this particular task that requires that it be dynamic, so I have to ask why you want it to be dynamic. If you do need it to be dynamic, you're going to have to give us more details which parts need to be dynamic and where that information is coming from.
Also, your questions betrays a gross misunderstanding of how your current code is working. Specifically, the STUFF function is not doing the concatenation. Since the solution is likely to be based on your current solution, you need to have a thorough understanding of how the current solution is working before proceeding.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 17, 2017 at 10:57 am
Based on empno i want to concatenate those 3 columns, by using stuff..XML PATH, not required to be in dynamic SQL..Please help me in suggesting the query
April 17, 2017 at 11:32 am
Not sure why you need XML and STUFF use, but this alternative will at least get you what you asked for:
CREATE TABLE
concat_test
(empno int
,jobid int
,vesselno int
,portid int
)
;
INSERT
concat_test
VALUES
(1,121,1455,1231)
,(1,122,1486,1322)
,(1,123,1496,1456)
,(2,121,1455,1231)
,(2,122,1486,1322)
,(2,123,1496,1456)
;
CREATE FUNCTION
udf_Concat_jobid
(@empno
INT
)
RETURNS
VARCHAR (8000)
BEGIN
DECLARE
@x
VARCHAR (8000)
= ''
;
SELECT @x += ',' + CAST (jobid AS VARCHAR(10))
FROM concat_test
WHERE empno = @empno
;
RETURN SUBSTRING (@x, 2, 8000)
;
END
;
GO
CREATE FUNCTION
udf_Concat_vesselno
(@empno
INT
)
RETURNS
VARCHAR (8000)
BEGIN
DECLARE
@x
VARCHAR (8000)
= ''
;
SELECT @x += ',' + CAST (vesselno AS VARCHAR(10))
FROM concat_test
WHERE empno = @empno
;
RETURN SUBSTRING (@x, 2, 8000)
;
END
;
GO
CREATE FUNCTION
udf_Concat_portid
(@empno
INT
)
RETURNS
VARCHAR (8000)
BEGIN
DECLARE
@x
VARCHAR (8000)
= ''
;
SELECT @x += ',' + CAST (portid AS VARCHAR(10))
FROM concat_test
WHERE empno = @empno
;
RETURN SUBSTRING (@x, 2, 8000)
;
END
;
GO
SELECT
empno
,dbo.udf_Concat_jobid (empno)
,dbo.udf_Concat_vesselno (empno)
,dbo.udf_Concat_portid (empno)
FROM
concat_test
GROUP BY
empno
;
April 17, 2017 at 12:31 pm
This should get you going ....
CREATE TABLE #Data (
empno int
, jobid int
, vesselno int
, portid int
);
INSERT INTO #Data ( empno, jobid, vesselno, portid )
VALUES ( 1, 121, 1455, 1231 )
, ( 1, 122, 1486, 1322 )
, ( 1, 123, 1496, 1456 )
, ( 2, 232, 1586, 1462 )
, ( 2, 233, 1596, 1556 );
SELECT
empno
, jobid = STUFF(CONVERT(VARCHAR(MAX), (SELECT ',' + CONVERT(VARCHAR(20), l.jobid)
FROM #Data AS l
WHERE l.empno = d.empno
-- NOTE: Order by all 3 fields to ensure consistency in all 3 lists
ORDER BY l.jobid, l.vesselno, l.portid
FOR XML PATH(''), TYPE
)), 1, 1, '')
, vesselno = STUFF(CONVERT(VARCHAR(MAX), (SELECT ',' + CONVERT(VARCHAR(20), l.vesselno)
FROM #Data AS l
WHERE l.empno = d.empno
-- NOTE: Order by all 3 fields to ensure consistency in all 3 lists
ORDER BY l.jobid, l.vesselno, l.portid
FOR XML PATH(''), TYPE
)), 1, 1, '')
, portid = STUFF(CONVERT(VARCHAR(MAX), (SELECT ',' + CONVERT(VARCHAR(20), l.portid)
FROM #Data AS l
WHERE l.empno = d.empno
-- NOTE: Order by all 3 fields to ensure consistency in all 3 lists
ORDER BY l.jobid, l.vesselno, l.portid
FOR XML PATH(''), TYPE
)), 1, 1, '')
FROM #Data AS d
GROUP BY empno
ORDER BY empno;
April 17, 2017 at 12:47 pm
Ronzo - Monday, April 17, 2017 11:32 AMNot sure why you need XML and STUFF use, but this alternative will at least get you what you asked for:
CREATE TABLE
concat_test
(empno int
,jobid int
,vesselno int
,portid int
)
;
INSERT
concat_test
VALUES
(1,121,1455,1231)
,(1,122,1486,1322)
,(1,123,1496,1456)
,(2,121,1455,1231)
,(2,122,1486,1322)
,(2,123,1496,1456)
;
CREATE FUNCTION
udf_Concat_jobid
(@empno
INT
)
RETURNS
VARCHAR (8000)
BEGIN
DECLARE
@x
VARCHAR (8000)
= ''
;
SELECT @x += ',' + CAST (jobid AS VARCHAR(10))
FROM concat_test
WHERE empno = @empno
;
RETURN SUBSTRING (@x, 2, 8000)
;
END
;
GO
CREATE FUNCTION
udf_Concat_vesselno
(@empno
INT
)
RETURNS
VARCHAR (8000)
BEGIN
DECLARE
@x
VARCHAR (8000)
= ''
;
SELECT @x += ',' + CAST (vesselno AS VARCHAR(10))
FROM concat_test
WHERE empno = @empno
;
RETURN SUBSTRING (@x, 2, 8000)
;
END
;
GO
CREATE FUNCTION
udf_Concat_portid
(@empno
INT
)
RETURNS
VARCHAR (8000)
BEGIN
DECLARE
@x
VARCHAR (8000)
= ''
;
SELECT @x += ',' + CAST (portid AS VARCHAR(10))
FROM concat_test
WHERE empno = @empno
;
RETURN SUBSTRING (@x, 2, 8000)
;
END
;
GO
SELECT
empno
,dbo.udf_Concat_jobid (empno)
,dbo.udf_Concat_vesselno (empno)
,dbo.udf_Concat_portid (empno)
FROM
concat_test
GROUP BY
empno
;
First, a scalar UDF will generally perform horribly.
Second, some environments are set up to prevent the creation of UDFs.
Third, you've hard-coded the table and field into the functions, which greatly limits their re-usability (which is why you had to create three of them).
Finally, the XML concatenation method has proven to be very efficient. Any other method needs to provide a compelling reason to use it over the XML method.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 17, 2017 at 12:51 pm
Sorry, I posted before I actually added comments.
There were a couple of comments I had about Des' solution.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 17, 2017 at 2:58 pm
drew.allen - Monday, April 17, 2017 12:51 PMSorry, I posted before I actually added comments.There were a couple of comments I had about Des' solution.
- The main query should use your employee table. This will eliminate the need for an expensive GROUP BY (or DISTINCT).
- You should use the .value method on the XML results of the subqueries. This isn't necessary in this case, because your data doesn't contain any characters that might be entitized, but it certainly doesn't hurt to get in the habit of doing this.
Drew
Thanks for the .value reminder Drew
The code below uses the XML.value method
SELECT
empno
, jobid = STUFF((SELECT ',' + CONVERT(VARCHAR(20), l.jobid)
FROM #Data AS l
WHERE l.empno = d.empno
-- NOTE: Order by all 3 fields to ensure consistency in all 3 lists
ORDER BY l.jobid, l.vesselno, l.portid
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 1, '')
, vesselno = STUFF((SELECT ',' + CONVERT(VARCHAR(20), l.vesselno)
FROM #Data AS l
WHERE l.empno = d.empno
-- NOTE: Order by all 3 fields to ensure consistency in all 3 lists
ORDER BY l.jobid, l.vesselno, l.portid
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 1, '')
, portid = STUFF((SELECT ',' + CONVERT(VARCHAR(20), l.portid)
FROM #Data AS l
WHERE l.empno = d.empno
-- NOTE: Order by all 3 fields to ensure consistency in all 3 lists
ORDER BY l.jobid, l.vesselno, l.portid
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 1, '')
FROM #Data AS d
GROUP BY empno
ORDER BY empno;
April 17, 2017 at 3:07 pm
April 17, 2017 at 3:53 pm
Here is a different solution that can build the string with a single pass at the table... (making it @ 1/3 the cost of the 3 pass solution).
IF OBJECT_ID('tempdb..#Emp', 'U') IS NOT NULL
DROP TABLE #Emp;
CREATE TABLE #Emp (
empno INT
);
INSERT #Emp (empno) VALUES (1), (2);
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
empno INT,
jobid INT,
vesselno INT,
portid INT
);
INSERT #TestData (empno,jobid,vesselno,portid) VALUES
(1,121,1455,1231),
(1,122,1486,1322),
(1,123,1496,1456),
(2,232,1586,1462),
(2,233,1596,1556);
--===============================================
SELECT
e.empno,
ConcatValue = CONCAT(e.empno, cv.ConcatValue)
FROM
#Emp e
CROSS APPLY (
SELECT
CONCAT(' | ', x.UnpivotedValues)
FROM
#TestData td
CROSS APPLY ( VALUES (td.jobid, 1), (td.vesselno, 2), (td.portid, 3)) x (UnpivotedValues, ColType)
WHERE
e.empno = td.empno
ORDER BY
td.empno,
x.ColType,
td.jobid
FOR XML PATH('')
) cv (ConcatValue);
April 17, 2017 at 5:17 pm
Ronzo - Monday, April 17, 2017 3:07 PMDrew,
Thanks for feedback. I realized my solution was not ideal for performance, but was unsure of the final use (small one time data load or frequent use).
It takes no longer to do it right whether 1 time or for frequent use. If you write with performance in mind all the time, it will become second nature and you won't have to make such a justification. And, consider that you had to write 3 functions to do the one time thing if that's what it turns out to be.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2017 at 9:48 am
GA_SQL - Monday, April 17, 2017 8:32 AMI want to concatenate below table rows using dynamic SQLempno | jobid | vesselno | portid
1 121 1455 1231
1 122 1486 1322
1 123 1496 1456output required is
empno | jobid | vesselno | portid
1 | 121,122,123 |1455,1486,1496 | 1231,1322,1456I tried using stuff command for empno and jobno it is working fine, but i want it for vesselno and portid as well, also same thing i have to complete it using dynamic SQL
Select
distinct A.empno,
--K.jobid,
jobno = STUFF(CAST((
SELECT [text()] = ', ' + ev.jobid
FROM
I've looked at several of your post and your missing the fundamentals. You don't post DDL, as per basic netiquette, so we have to do it for you. What you do post has no keys – did you know that a table must have a key, by definition? Your other postings have confused rows and records, tables and files, etc.
In this particular posting, you miss the whole idea of normal forms. We don't use lists in SQL! Most of your code has actually been 1950's COBOL written using highly proprietary SQL. A forum is not a good place to pick up a basic education.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 18, 2017 at 9:56 am
Jason A. Long - Monday, April 17, 2017 3:53 PMHere is a different solution that can build the string with a single pass at the table... (making it @ 1/3 the cost of the 3 pass solution).
IF OBJECT_ID('tempdb..#Emp', 'U') IS NOT NULL
DROP TABLE #Emp;CREATE TABLE #Emp (
empno INT
);
INSERT #Emp (empno) VALUES (1), (2);IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;CREATE TABLE #TestData (
empno INT,
jobid INT,
vesselno INT,
portid INT
);
INSERT #TestData (empno,jobid,vesselno,portid) VALUES
(1,121,1455,1231),
(1,122,1486,1322),
(1,123,1496,1456),
(2,232,1586,1462),
(2,233,1596,1556);--===============================================
SELECT
e.empno,
ConcatValue = CONCAT(e.empno, cv.ConcatValue)
FROM
#Emp e
CROSS APPLY (
SELECT
CONCAT(' | ', x.UnpivotedValues)
FROM
#TestData td
CROSS APPLY ( VALUES (td.jobid, 1), (td.vesselno, 2), (td.portid, 3)) x (UnpivotedValues, ColType)
WHERE
e.empno = td.empno
ORDER BY
td.empno,
x.ColType,
td.jobid
FOR XML PATH('')
) cv (ConcatValue);
Except that this produces a single column whereas the desired output is three columns.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 18, 2017 at 10:48 am
I came up with the following:
IF OBJECT_ID('tempdb..#Emp', 'U') IS NOT NULL
DROP TABLE #Emp;
CREATE TABLE #Emp (
empno INT
);
INSERT #Emp (empno) VALUES (1), (2);
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
empno INT,
jobid INT,
vesselno INT,
portid INT
);
INSERT #TestData (empno,jobid,vesselno,portid) VALUES
(1,121,1455,1231),
(1,122,1486,1322),
(1,123,1496,1456),
(2,232,1586,1462),
(2,233,1596,1556);
select * from #TestData;
with Emps as (
select distinct
empno
from
#TestData
)
select
e.empno
, ca1.jobid
, ca2.vesselno
, ca3.portid
from
Emps e
cross apply (select STUFF((select ',' + cast(td.jobid as varchar(10)) from #TestData td where td.empno = e.empno for xml path(''),TYPE).value('.','varchar(max)'),1,1,'')) ca1(jobid)
cross apply (select STUFF((select ',' + cast(td.vesselno as varchar(10)) from #TestData td where td.empno = e.empno for xml path(''),TYPE).value('.','varchar(max)'),1,1,'')) ca2(vesselno)
cross apply (select STUFF((select ',' + cast(td.portid as varchar(10)) from #TestData td where td.empno = e.empno for xml path(''),TYPE).value('.','varchar(max)'),1,1,'')) ca3(portid);
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply