April 11, 2011 at 8:34 am
I am sure there are many ways to do this ....so here is one idea
insert into Table A (col1)
SELECT B.Col1 + B.Col2 + B.Col3.......
FROM Table B
April 11, 2011 at 8:51 am
This should do the trick:
DECLARE @code_positions nvarchar(max)
DECLARE @column_names nvarchar(max)
DECLARE @sql nvarchar(max)
SET @code_positions =
STUFF
(
(
SELECT ',' + QUOTENAME(CAST(Number AS varchar(2))) AS [text()]
FROM master.dbo.spt_values
WHERE type = 'P'
AND Number BETWEEN 1 AND 50
ORDER BY Number
FOR XML PATH('')
)
, 1, 1, SPACE(0));
SET @column_names =
STUFF
(
(
SELECT ',' + QUOTENAME(CAST(Number AS varchar(2))) + ' AS CODE_POS_' + CAST(Number AS varchar(2)) AS [text()]
FROM master.dbo.spt_values
WHERE type = 'P'
AND Number BETWEEN 1 AND 50
ORDER BY Number
FOR XML PATH('')
)
, 1, 1, SPACE(0));
PRINT @column_names
SET @sql = '
SELECT CRN, ' + @column_names + '
FROM (
SELECT CRN, CODE, CODE_POSITION
FROM PS_TestForOnline
)AS T
PIVOT ( MIN(CODE) FOR CODE_POSITION IN ('+ @code_positions +')) AS P'
PRINT @sql
EXEC(@sql)
Basically, it's just a dynamic pivot, built with dynamic sql.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply