March 23, 2011 at 9:17 am
WITH CTE( ProblemID,Who , seq )
AS ( SELECT .problemId, X.Who,
ROW_NUMBER() OVER ( PARTITION BY ProblemID ORDER BY Who)
FROM X )
SELECT ProblemId,
MAX( CASE seq WHEN 1 THEN Who ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN Who ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN Who ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 4 THEN Who ELSE '' END ) AS newColumn
FROM CTE
GROUP BY ProblemID;
Result of the query above combines three colimns named who into one and i want to update the newcolumn into a single column who on the table x and get an error as
invalid object as CTE
Update X
SET X.who =
( select CTE.newColumn from CTE where
X.problemID = CTE.problemId)
where exists
(select CTE.newColumn from CTE where
X.problemID = CTE.problemId)
March 23, 2011 at 9:28 am
Can't update original cte after the first select statement. Try adding a second cte something like this:
WITH CTE (ProblemID, Who, seq)
AS
(
SELECT X.problemId, X.Who,
ROW_NUMBER() OVER (PARTITION BY ProblemID ORDER BY Who)
FROM X
)
,
CTE2 (ProblemID, newColumn)
AS
(
SELECT ProblemId,
MAX( CASE seq WHEN 1 THEN Who ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN Who ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN Who ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 4 THEN Who ELSE '' END ) AS newColumn
FROM CTE
GROUP BY ProblemID
)
Update X
SET X.who = (
select CTE2.newColumn
from CTE2
where X.problemID = CTE2.problemId
)
where exists (
select CTE2.newColumn
from CTE2
where X.problemID = CTE2.problemId
)
There is an exception to every rule, except this one...
March 23, 2011 at 10:07 am
Thanks that worked, thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply