September 2, 2009 at 12:20 pm
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.
September 2, 2009 at 1:28 pm
Never mind, i got it.
April 21, 2010 at 5:03 pm
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
April 22, 2010 at 12:13 am
lol, nevermind it too)))))))))))))
April 22, 2010 at 1:04 am
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
April 22, 2010 at 4:25 am
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
April 22, 2010 at 6:48 am
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!!
January 8, 2011 at 2:06 am
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! 🙂 :w00t:
Best regards.
March 6, 2013 at 12:58 pm
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 ;
March 6, 2013 at 1:06 pm
For XML Path is not supported in sql 2000.
March 6, 2013 at 1:19 pm
What code should I use in SQL 2000 then?
March 6, 2013 at 2:35 pm
something along the lines of...
Declare @assignTo nvarchar(4000)
select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
from
table2
where
...
Select @assignTo
but fair warning that won't work in a subquery..to use it in a correlated subquery you'll have to write it into a UDF with table access.
Umm....as far as I know. It's been a while.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply