April 25, 2008 at 1:56 am
Experts,
I have one senario.
Empno Acc/No salary
100 1000 4000
100 2000 5000
100 3000 6000
200 1000 4000
200 6500 7000
I need the output like
Empno Acc/No salary
100 1000, 2000, 3000 4000, 5000, 6000
200 1000, 6500 4000, 7000
Inputs are welcome !
karthik
April 25, 2008 at 2:50 am
Hi,
For a quick solution you could use a cursor ( yes, I know, I said Cursor :crazy: ) like the one below:
-- Test Environment
DECLARE @table TABLE (
empNo INT,
accNo INT,
salary INT
)
INSERT INTO @table VALUES(100, 1000, 4000)
INSERT INTO @table VALUES(100, 2000, 5000)
INSERT INTO @table VALUES(100, 3000, 6000)
INSERT INTO @table VALUES(200, 1000, 4000)
INSERT INTO @table VALUES(200, 6500, 7000)
-- Solution
DECLARE @results TABLE (
empNo INT,
accNo VARCHAR(50),
salary VARCHAR(50))
DECLARE @empNo INT
DECLARE @strAccNo VARCHAR(50)
DECLARE @strSalary VARCHAR(50)
DECLARE csr_Employee CURSOR FOR
SELECT DISTINCT empNo
FROM @table
OPEN csr_Employee
FETCH NEXT FROM csr_Employee INTO @empNo
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strAccNo = '',
@strSalary = ''
-- Build Comma Delimited Strings
SELECT @strAccNo = @strAccNo + CAST(accNo AS VARCHAR) + ', ',
@strSalary = @strSalary + CAST(salary AS VARCHAR) + ', '
FROM @table
WHERE empNo = @empNo
-- Trim Trailing Commas
SELECT @strAccNo = LEFT(@strAccNo, LEN(@strAccNo) - 1),
@strSalary = LEFT(@strSalary, LEN(@strSalary) - 1)
-- Store results
INSERT INTO @results SELECT @empNo, @strAccNo, @strSalary
FETCH NEXT FROM csr_Employee INTO @empNo
END
CLOSE csr_Employee
DEALLOCATE csr_Employee
-- Return Results
SELECT * FROM @results
I'm pretty sure that there's going to be a set based way of doing this though.
HTH for now,
April 25, 2008 at 4:28 am
Hi Adrian,
Thanks. I have modified your code.it is working perfectly.
------------------------------------------------------
CREATE TABLE #results
(
empNo INT,
accNo VARCHAR(50),
salary VARCHAR(50)
)
insert into #results values(100, '1000', '10000')
insert into #results values(100, '2000', '20000')
insert into #results values(100, '3000', '30000')
insert into #results values(200, '2500', '35000')
insert into #results values(200, '3500', '35000')
CREATE PROC SAMPLE
AS
BEGIN
DECLARE @empNo INT
DECLARE @strAccNo VARCHAR(50)
DECLARE @strSalary VARCHAR(50)
DECLARE csr_Employee CURSOR FOR
SELECT empNo,accNo,salary
FROM #results
--To store results
CREATE TABLE #results1
(
empNo INT,
accNo VARCHAR(50),
salary VARCHAR(50)
)
--Open
OPEN csr_Employee
FETCH csr_Employee INTO @empNo,@strAccNo,@strSalary
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @PrevempNo int
if isnull(@PrevempNo,0) = @empNo
Begin
Print 'Update...'
Update #results1
set accNo = accNo + ','+@strAccNo,salary = salary +','+@strSalary
where empNo = @empNo
End
Else
Begin
print' Insert...'
Insert into #results1
select @empNo,@strAccNo,@strSalary
End
select @PrevempNo = @empNo
print 'Assign part...'
FETCH csr_Employee INTO @empNo,@strAccNo,@strSalary
END
CLOSE csr_Employee
DEALLOCATE csr_Employee
SELECT * FROM #results1
END
--------------------------------------------------------------------
It gaves me the expected output exactly. But i am not happy with CURSOR Logic.I want to apply RBAR Logic.
Experts sugestions and inputs are welcome !
karthik
April 25, 2008 at 5:10 am
You could use UDFs instead of a cursor to perform the concatenation of each column (fnConcatEmpAccNo and fnConcatEmpSalary) which executes pretty much the same code as the cursor on each column AccNo and Salary. This would tidy up the coding and rid you of the dreaded cursor. An example of fnConcatEmpAccNo is below:
-- Test Environment
CREATE TABLE emp (
empNo INT,
accNo INT,
salary INT
)
INSERT INTO emp VALUES(100, 1000, 4000)
INSERT INTO emp VALUES(100, 2000, 5000)
INSERT INTO emp VALUES(100, 3000, 6000)
INSERT INTO emp VALUES(200, 1000, 4000)
INSERT INTO emp VALUES(200, 6500, 7000)
GO
-- Solution
CREATE FUNCTION fnConcatEmpAccNo(
@empNo INT
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @a VARCHAR(50)
SET @a = ''
SELECT @a = @a + CAST(accNo AS VARCHAR) + ', '
FROM emp
WHERE empNo = @empNo
SELECT @a = LEFT(@a, LEN(@a) - 1)
RETURN @a
END
GO
SELECT DISTINCT empNo, dbo.fnConcatEmpAccNo(empNo)
FROM emp
--Tidy Up
DROP TABLE emp
DROP FUNCTION fnConcatEmpAccNo
Certainly beats a cursor!! 😀
April 25, 2008 at 5:28 am
Adrian,
You are correct. But i don't have the permission to create functions. Can you provide some other alternate ways ?
karthik
April 25, 2008 at 5:46 am
Just out of interest while I try and figure another way:
How many rows does this table have?
Hom many rows are there maximum (if there is a maximum) does each employee have?
Thanks,
April 25, 2008 at 5:50 am
How many rows does this table have?
50000 rows.
Hom many rows are there maximum (if there is a maximum) does each employee have?
10-15
karthik
April 25, 2008 at 6:13 am
Thanks for that.
The only other ways that I can currently think of is to use a WHILE loop to UPDATE a resultset table to build the strings or the other way requires SQL Server 2005 to use FOR XML PATH('').
Apart from those, I'm currently stuck! :crazy:
April 25, 2008 at 6:18 am
I am also thought to use WHILE LOOP.
Adrian, do you think is there any performance wise difference between using CURSOR and WHILE loop ?
Please share your thoughts.
karthik
April 25, 2008 at 6:19 am
SQL Server 2005 to use FOR XML PATH('').
But i am using sql2000.
karthik
April 25, 2008 at 6:54 am
do you think is there any performance wise difference between using CURSOR and WHILE loop ?
It's a tough call, it's one of those things that can swing either way depending on the task. The only way to find out is test both way on your task. :ermm:
April 25, 2008 at 6:57 am
karthikeyan (4/25/2008)
Adrian,You are correct. But i don't have the permission to create functions. Can you provide some other alternate ways ?
You could either ask for the privs or get the DBA to review and run the code that makes the function. The answer is always "No" unless you ask. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2008 at 7:00 am
karthikeyan (4/25/2008)
I am also thought to use WHILE LOOP.Adrian, do you think is there any performance wise difference between using CURSOR and WHILE loop ?
Please share your thoughts.
Cursor (with FAST FORWARD option) and While loops have the same performance problems.
Talk with the DBA and explain, then ask for the UDF to be instantiated.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply