Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

combine multiple rows into one dynamically query question Expand / Collapse
Author
Message
Posted Wednesday, September 2, 2009 12:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:56 PM
Points: 74, Visits: 264
Hi, I have the below table, and I need to combine them
into one records dynamically (I'm not sure I'm clear on this,
so maybe you look at the below table is better).
create table Test
(
id int,
name varchar(30),
counts int
)

insert into Test
select 1, 'Math', 5
union
select 1, 'Literature', 3
union
select 1, 'Cooking', 1
union
select 1, 'Reading', 2

I would like the result to look like this below,
id categorycounts
1 Cooking (1), Literature (3), Math(5), Reading(2)

the challenge I have is the id can have multiple values like this, how do I know to combine them all dynamically and correctly. thanks a lot if you could help and give any input.
Post #781712
Posted Wednesday, September 2, 2009 1:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:56 PM
Points: 74, Visits: 264
Never mind, i got it.
Post #781772
Posted Wednesday, April 21, 2010 5:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:04 PM
Points: 59, Visits: 337
Yes, but can you - or anyone - share your wisdom?

Let's say there is a table like below

KeyField..............DescField
1....................... 'A'
1....................... 'B'
1....................... 'C'
2.......................'D'
2.......................'E'
2.......................'F'

and I need a single select returning

1.........'A,B,C'
2.........'D,E,F'

?


Thank you

Post #908221
Posted Thursday, April 22, 2010 12:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:04 PM
Points: 59, Visits: 337
lol, nevermind it too)))))))))))))

Post #908335
Posted Thursday, April 22, 2010 1:04 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:14 AM
Points: 196, Visits: 656
Hi,

Try this...
--------------------------------------------------------
DECLARE @lValueStr VARCHAR(MAX)
SET @lValueStr = NULL
SELECT @lValueStr = COALESCE(@lValueStr + ', ', '')
+ name + '(' + CAST(Counts AS VARCHAR(5)) + ')'
FROM Test T
SELECT @lValueStr
---------------------------------------------------------

Shaiju C.K.


_____________________________________________
One ounce of practice is more important than tonnes of dreams
Post #908353
Posted Thursday, April 22, 2010 4:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 2:08 PM
Points: 405, Visits: 2,670
I had already done a PIVOT when I saw you'd already got it!

;WITH ctePrepare
AS
(SELECT id, name, name + ' (' + CONVERT(VARCHAR, counts) + ')' AS NameWithCount FROM test)
SELECT id, Cooking + ', ' + Literature + ', ' + Math + ', ' + Reading AS categorycounts
FROM
(SELECT id, name, NameWithCount FROM ctePrepare) AS Z
PIVOT (MAX(NameWithCount) FOR name IN (Cooking, Literature, Math, Reading)) AS pvt

Post #908447
Posted Thursday, April 22, 2010 6:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 PM
Points: 2,262, Visits: 5,405
This might be helpful for some who accidentally tumbles upon this thread

IF OBJECT_ID('TEMPDB..#CONCAT_COLUMN_VALUES') IS NOT NULL
DROP TABLE #CONCAT_COLUMN_VALUES

CREATE TABLE #CONCAT_COLUMN_VALUES
(
GROUP_ID INT,
COL_VAL VARCHAR(5)
)

INSERT INTO #CONCAT_COLUMN_VALUES
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
SELECT 1, 'C' UNION ALL
SELECT 2, 'D' UNION ALL
SELECT 2, 'E' UNION ALL
SELECT 3, 'F' UNION ALL
SELECT 4, 'G'

SELECT * FROM #CONCAT_COLUMN_VALUES

SELECT p1.GROUP_ID,
( SELECT COL_VAL + ','
FROM #CONCAT_COLUMN_VALUES p2
WHERE p2.GROUP_ID = p1.GROUP_ID
ORDER BY COL_VAL
FOR XML PATH('')
) AS Concat_Values
FROM #CONCAT_COLUMN_VALUES p1
GROUP BY p1.GROUP_ID ;

Cheers!!
Post #908544
Posted Saturday, January 8, 2011 2:06 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:04 PM
Points: 11, Visits: 75
Thanks to everybody!!!; I was testing this script in SQL Server 2005 and everthing is ok.

//>>>>
DECLARE @lValueStr VARCHAR(MAX)
SET @lValueStr = NULL

SELECT @lValueStr = COALESCE(@lValueStr + ', ', '')
+ a.Cod_Asignatura + '(' + CAST(rtrim(ltrim(b.Des_Asignatura)) AS VARCHAR(30)) + ')'
FROM pla_tt_PlanificacionDetAsig a inner join pla_tc_Asignatura b
on a.cod_empresa = b.cod_empresa
and a.cod_recinto = b.cod_recinto
and a.cod_asignatura = b.cod_asignatura

SELECT @lValueStr
<<<<//

I Really don't understan how it work, but just work and good!


Best regards.
Post #1044852
Posted Wednesday, March 6, 2013 12:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:38 PM
Points: 64, Visits: 127
Hello,

I tried this code in SQL 2000 but get this error: "Incorrect syntax near the keyword 'FOR'." Why is that?

IF OBJECT_ID('TEMPDB..#IF_BestPriceMfg2') IS NOT NULL
DROP TABLE #IF_BestPriceMfg2

CREATE TABLE #IF_BestPriceMfg2
(
GlobalCustomer INT,
Productline_ID VARCHAR(6)
)

INSERT INTO #IF_BestPriceMfg2
SELECT GlobalCustomer
,Productline_ID
FROM #IF_BestPriceMfg
GROUP BY GlobalCustomer
,Productline_ID

SELECT * FROM #IF_BestPriceMfg2

SELECT p1.GlobalCustomer,
( SELECT Productline_ID + ','
FROM #IF_BestPriceMfg2 p2
WHERE p2.GlobalCustomer = p1.GlobalCustomer
ORDER BY Productline_ID
FOR XML PATH('')
) AS Concat_Values
FROM #IF_BestPriceMfg2 p1
GROUP BY p1.GlobalCustomer ;
Post #1427597
Posted Wednesday, March 6, 2013 1:06 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
For XML Path is not supported in sql 2000.
Post #1427602
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse