Staging the Value

[benchpolo]

Data:
Acct Value
00011 Dermatologist
00011 Oncology
00011 General
00012 Part A
00012 Part B
00012 Part C

Question?
How can I stage the above data where the value will appear in the same line (see below)

00011 | Dermatologist | Oncology | General
00012 | Part A | Part B | Part C

Thanks.

[ck9663]

you need a PIVOT table query...

check this post

-- ck

[benchpolo]

Actually, it is more like a SQL Scripting bec. i'm pulling data from our server table.

[deepuv04]

Actually, it is more like a SQL Scripting bec. i'm pulling data from our server table.

hi,
you can do this using function as


-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION fun
(
@acct varchar(10)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
declare @value varchar(max)


DECLARE @TABLE TABLE
( ID INT identity(1,1),Acct VARCHAR(10),Value VARCHAR(100))
INSERT INTO @Table (Acct,Value)
SELECT Acct,Value FROM T1 WHERE Acct = @acct

select @value = ' '

DECLARE @MIN INT,@MAX INT
SELECT @MIN = min(id),@max = max(id) from @table
while( @min <= @max)
begin
--select @value = @value + '''' + Acct + ''',''' + [value] + ''' UNION select '
select @value = @value +'''' + [value] +''','
from @table
where id = @min
set @min = @min + 1
end

return SUBSTRING(@value,0,LEN(@VALUE))
END
GO



DECLARE @STRING VARCHAR(MAX)

SET @STRING = ''
SELECT @STRING = @STRING + 'SELECT '+ ACCT +','+ dbo.fun(acct) + ' UNION '
FROM T1
GROUP BY ACCT

--PRINT @STRING

SELECT @STRING = SUBSTRING(@String,0,len(@String) - (len('UNION')))
EXEC (@STRING)


this query will work only if each acct has equal number of rows. change this query according to your requirement.

thanks