Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
concatenating variable number of strings into one string

Answered concatenating variable number of strings into one string

  • Friday, January 04, 2013 4:54 PM
     
      Has Code
    Sign In to Vote
    0

    Hi - Does anyone know how to generate the results shown in the commented section below using the data provided in this sample?

    declare @t table (OutputID int, InputID varchar(200), Quantity decimal(8,2));
insert into @t values
(1000, '5057A', 14.3),
(1000, '5364', 8.9),
(1001, '5762', 19.7),
(1001, '5611', 20.1),
(1001, '5498', 15.3),
(1002, '5578B', 6.3),
(1003, '5821', 4.7),
(1003, '5798', 5.0);
select * from @t;
go
/*
desired result of query:
OutputID   All_InputIDs
1000       5057A + 5364
1001       5762 + 5611 + 5498
1002       5578B
1003       5821 + 5798
*/


    • Edited by Knot Friday, January 04, 2013 5:17 PM
    •  
     

All Replies

  • Friday, January 04, 2013 4:59 PM
     
      Has Code
    Sign In to Vote
    0

    Try this:

    declare @t table (OutputID int, InputID varchar(20), Quantity decimal(8,2));
insert into @t values
(1000, '5057A', 14.3),
(1000, '5364', 8.9),
(1001, '5762', 19.7),
(1001, '5611', 20.1),
(1001, '5498', 15.3),
(1002, '5578B', 6.3),
(1003, '5821', 4.7),
(1003, '5798', 5.0);
select * from @t;

SELECT a.OutputID, stuff( (
		SELECT '+' + InputID 
		FROM @t b
		where a.OutputID = b.OutputID
		for xml path('') ) ,1,1,'' ) 
	 as m
	FROM @t a
	GROUP by a.OutputID

    Regards!

    Sergio Sánchez Arias

     
  • Friday, January 04, 2013 4:59 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Try

    SELECT T.OutputID
	,stuff((
			SELECT ' + ' + InputID
			FROM @t T1
			WHERE T1.OutputID = T.OutputID
			ORDER BY InputID
			FOR XML PATH('')
			), 1, 3, '') AS [All_InputIDs]
FROM @t T
GROUP BY T.OutputID
ORDER BY T.OutputID

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked As Answer by Knot Friday, January 04, 2013 5:16 PM
    •  
     
  • Friday, January 04, 2013 5:16 PM
     
     
    Sign In to Vote
    0
    This works beautifully!  Thank-you.
     
  • Monday, January 07, 2013 1:59 PM
     
      Has Code
    Sign In to Vote
    0

    How is this done when the primary key is a composite primary key?  Here is the example with a CPK:

    declare @t table (OutputID int, OutputVersion int, InputID varchar(200), Quantity decimal(8,2));
insert into @t values
(1000, 1, '5057A', 14.3),
(1000, 1, '5364', 8.9),
(1001, 1, '5762', 19.7),
(1001, 1, '5611', 20.1),
(1001, 2, '5498', 15.3),
(1002, 1, '5578B', 6.3),
(1003, 1, '5821', 4.7),
(1003, 1, '5798', 5.0);
select * from @t;
/*
desired result of query:
OutputID OutputVersion  All_InputIDs
1000       1            5057A + 5364
1001       1            5762 + 5611
1001       2            5498
1002       1            5578B
1003       1            5821 + 5798
*/

     
  • Monday, January 07, 2013 2:02 PM
     
      Has Code
    Sign In to Vote
    0

    Same as above

    SELECT T.OutputID
	,stuff((
			SELECT ' + ' + InputID
			FROM @t T1
			WHERE T1.OutputID = T.OutputID
                               AND T1.OutputVersion = T.OutputVersion
			ORDER BY InputID
			FOR XML PATH('')
			), 1, 3, '') AS [All_InputIDs]
FROM @t T
GROUP BY T.OutputID
ORDER BY T.OutputID

     
  • Monday, January 07, 2013 2:24 PM
     
     
    Sign In to Vote
    0
    That does not give the desired result--see "desired result of query" in 1/7/13 post.
    • Edited by Knot Monday, January 07, 2013 2:24 PM
    •  
     
  • Monday, January 07, 2013 3:27 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0
     
    SELECT T.OutputID
	,stuff((
			SELECT ' + ' + InputID
			FROM @t T1
			WHERE T1.OutputID = T.OutputID  AND T1.OutputVersion = T.OutputVersion
			ORDER BY InputID
			FOR XML PATH('')
			), 1, 3, '') AS [All_InputIDs]
FROM @t T
GROUP BY T.OutputID ,T.OutputVersion
ORDER BY T.OutputID

    • Marked As Answer by Knot Monday, January 07, 2013 3:43 PM
    •