March 22, 2011 at 2:52 pm
i have a table in which i have records like
Table P
ProblemID Desc
1 this is a test
1 this is another test
1 this is another test
now to concatenate them i used
WITH CTE ( ProblemID,OverallPlan , seq )
AS ( SELECT tableP.problemId, TableP.OverallPlan,
ROW_NUMBER() OVER ( PARTITION BY ProblemID ORDER BY OverallPlan )
FROM tableP )
SELECT ProblemId,
MAX( CASE seq WHEN 1 THEN OverallPlan ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN OverallPlan ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN OverallPlan ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 4 THEN OverallPlan ELSE '' END )
FROM CTE
GROUP BY ProblemID;)
This provides me with the select but how can i update the select into a single overallplan column and then deleted any repeated ID's
March 23, 2011 at 2:50 pm
I'm not completely sure what you are trying to do, but have you tried using the FOR XML PATH('') technique?
CREATE TABLE #P (ProblemID int, OverallPlan varchar(50))
INSERT #P (ProblemID, OverallPlan)
SELECT 1, 'this is a test' UNION ALL
SELECT 1, 'this is another test' UNION ALL
SELECT 1, 'this is another test' UNION ALL
SELECT 2, 'test & <2a>' UNION ALL
SELECT 2, 'test " <2b>'
SELECT
POUTER.ProblemID,
STUFF((
SELECT '; ' + PINNER.OverallPlan FROM #P PINNER
WHERE PINNER.ProblemID = POUTER.ProblemID
ORDER BY PINNER.OverallPlan
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),
1, 2, '') AS ConcatenatedList
FROM #P POUTER
GROUP BY POUTER.ProblemID
ORDER BY POUTER.ProblemID
March 24, 2011 at 10:30 pm
what i am actually trying to do is
update a table X from a different table Y which has multiple records with the same ID i.e.
Table y
Id name notification validation reason
1 test testnoti test test
1 testA testB testC testD
1 TestE testF testG TestH
and it has the same data pattern for Id 2 and so on
now what i am trying to do is
concatenate all the records in this table Y into table X i.e. all the records with ID 1 will be stored in ID 1 in table x as
Table X
ID newName newnotificaiton NewValidation newReason
1 test,testA,TestE testnoti,TestB,TestF test,testC,testG test,testD,testH
with common table expression i know i can do this within a table but i have no idea on how to do ut with two tables
March 25, 2011 at 7:46 am
In most circumstances it's not a good idea to store concatenated values in a base table. It will cause pain if you ever need to split the concatenated column into separate values again. However, if you have to do this, then you can just use the FOR XML PATH('') technique as part of an UPDATE (INSERT or MERGE) statement.
March 25, 2011 at 9:56 am
Storing multiple values in single column <> 'good' 🙂
However, there was an elegant bit of code on this site that I came across monday which may help.
Adapt for your purposes.
CREATE TABLE [dbo].[MyStatus](
[Status_Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[StatusDesc] [varchar](25) NULL,
)
INSERT INTO MyStatus VALUES ('Active')
INSERT INTO MyStatus VALUES ('OnHold')
INSERT INTO MyStatus VALUES ('Disabled')
INSERT INTO MyStatus VALUES ('Closed')
DECLARE @MyStatusList VARCHAR(1000)
SET @MyStatusList = ''
SELECT @MyStatusList = ISNULL(@MyStatusList,'') + StatusDesc + ',' FROM MyStatus ORDER BY Status_Id
PRINT @MyStatusList
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply