By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

Staging the Value

benchpolo
100+
P: 142
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.
Feb 6 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
you need a PIVOT table query...

check this post

-- ck
Feb 7 '08 #2

benchpolo
100+
P: 142
Actually, it is more like a SQL Scripting bec. i'm pulling data from our server table.
Feb 7 '08 #3

deepuv04
Expert 100+
P: 227
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
Feb 8 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.