Gurus Need your help!!!!
-
Gurus Pl. provide your help. Hre is the situation.
I have a table A with two columns
Name and SAL. with following Data.
TableA :-
Name SAl
Tom 20
Tom 30
TOM 40
Tom 50
Jen 30
Jen 20
Now I need to create a Table which will have distinct record of TableA and it should store the all salary values of Name. So the out put record in TABLE B should look like this.
TABLEB
NAME SAL
Tom 20,30,40,50
Jen 30,20
Can Any one provide me the sript which will provide me this output. Thanks fro you Guys help.
-
The easiest way for me, but maybe not the best in performance could be to use a couple of cursors:
CREATE TABLE #1
(Name VARCHAR(20),
SAl INT)
GO
INSERT INTO #1 VALUES ('Tom', 20)
GO
INSERT INTO #1 VALUES ('Tom', 30)
GO
INSERT INTO #1 VALUES ('TOM', 40)
GO
INSERT INTO #1 VALUES ('Tom', 50)
GO
INSERT INTO #1 VALUES ('Jen', 30)
GO
INSERT INTO #1 VALUES ('Jen', 20)
GO
CREATE TABLE #2
(Name VARCHAR(20),
SAl VARCHAR (300))
GO
DECLARE @Name AS VARCHAR(20),
@Sal AS INT,
@STR AS VARCHAR(300)
DECLARE curNames CURSOR LOCAL FOR
SELECT Name
FROM #1
OPEN curNames
FETCH NEXT FROM curNames INTO @Name
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE curSal CURSOR LOCAL FOR
SELECT Sal
FROM #1
WHERE Name = @Name
SELECT @STR = ''
OPEN curSal
FETCH NEXT FROM curSal INTO @Sal
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @STR = @STR + CONVERT(VARCHAR,@Sal) + ', '
FETCH NEXT FROM curSal INTO @Sal
END
CLOSE curSal
DEALLOCATE curSal
SELECT @STR = SUBSTRING (@str, 1, LEN(@str) - 1)
INSERT INTO #2 VALUES (@name, @STR)
FETCH NEXT FROM curNames INTO @Name
END
CLOSE curNames
DEALLOCATE curNames
SELECT * FROM #2
-
quote:
The easiest way for me, but maybe not the best in performance could be to use a couple of cursors:CREATE TABLE #1
(Name VARCHAR(20),
SAl INT)
GO
INSERT INTO #1 VALUES ('Tom', 20)
GO
INSERT INTO #1 VALUES ('Tom', 30)
GO
INSERT INTO #1 VALUES ('TOM', 40)
GO
INSERT INTO #1 VALUES ('Tom', 50)
GO
INSERT INTO #1 VALUES ('Jen', 30)
GO
INSERT INTO #1 VALUES ('Jen', 20)
GO
CREATE TABLE #2
(Name VARCHAR(20),
SAl VARCHAR (300))
GO
DECLARE @Name AS VARCHAR(20),
@Sal AS INT,
@STR AS VARCHAR(300)
DECLARE curNames CURSOR LOCAL FOR
SELECT Name
FROM #1
OPEN curNames
FETCH NEXT FROM curNames INTO @Name
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE curSal CURSOR LOCAL FOR
SELECT Sal
FROM #1
WHERE Name = @Name
SELECT @STR = ''
OPEN curSal
FETCH NEXT FROM curSal INTO @Sal
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @STR = @STR + CONVERT(VARCHAR,@Sal) + ', '
FETCH NEXT FROM curSal INTO @Sal
END
CLOSE curSal
DEALLOCATE curSal
SELECT @STR = SUBSTRING (@str, 1, LEN(@str) - 1)
INSERT INTO #2 VALUES (@name, @STR)
FETCH NEXT FROM curNames INTO @Name
END
CLOSE curNames
DEALLOCATE curNames
SELECT * FROM #2
OR You might get rid of One of those cursors and use:
CREATE TABLE #TBL1
(Name VARCHAR(20),
SAl INT)
GO
INSERT INTO #TBL1 VALUES ('Tom', 20)
GO
INSERT INTO #TBL1 VALUES ('Tom', 30)
GO
INSERT INTO #TBL1 VALUES ('TOM', 40)
GO
INSERT INTO #TBL1 VALUES ('Tom', 50)
GO
INSERT INTO #TBL1 VALUES ('Jen', 30)
GO
INSERT INTO #TBL1 VALUES ('Jen', 20)
GO
CREATE TABLE #TBL2
(Name VARCHAR(20),
SAl VARCHAR (300))
GO
DECLARE @Name AS VARCHAR(20), @PrevName varchar (20),
@Sal AS INT,
@str AS VARCHAR(300)
SET @PrevName = ''
DECLARE curNames CURSOR LOCAL FOR
SELECT Name, SAl
FROM #TBL1 Order by Name
OPEN curNames
FETCH NEXT FROM curNames INTO @Name, @Sal
WHILE (@@FETCH_STATUS = 0)
BEGIN
if @PrevName <> @name
Insert Into #TBL2 (Name, SAl) VAlues (@Name, @Sal)
else
UPDATE #TBL2 SET SAL = SAL + ',' + CONVERT(VARCHAR,@Sal) WHERE Name = @Name
SET @PrevName = @Name
FETCH NEXT FROM curNames INTO @Name, @Sal
END
CLOSE curNames
DEALLOCATE curNames
SELECT * FROM #TBL2
DROP TABLE #TBL2
DROP TABLE #TBL1
For the SET BASED You will need The Maximum Of records per name and that still leaves you with a not very pretty query. I personaly think this is one of those cases where cursor (using the right indexes) are a good choice
* Noel -
quote:
quote:
The easiest way for me, but maybe not the best in performance could be to use a couple of cursors:CREATE TABLE #1
(Name VARCHAR(20),
SAl INT)
GO
INSERT INTO #1 VALUES ('Tom', 20)
GO
INSERT INTO #1 VALUES ('Tom', 30)
GO
INSERT INTO #1 VALUES ('TOM', 40)
GO
INSERT INTO #1 VALUES ('Tom', 50)
GO
INSERT INTO #1 VALUES ('Jen', 30)
GO
INSERT INTO #1 VALUES ('Jen', 20)
GO
CREATE TABLE #2
(Name VARCHAR(20),
SAl VARCHAR (300))
GO
DECLARE @Name AS VARCHAR(20),
@Sal AS INT,
@STR AS VARCHAR(300)
DECLARE curNames CURSOR LOCAL FOR
SELECT Name
FROM #1
OPEN curNames
FETCH NEXT FROM curNames INTO @Name
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE curSal CURSOR LOCAL FOR
SELECT Sal
FROM #1
WHERE Name = @Name
SELECT @STR = ''
OPEN curSal
FETCH NEXT FROM curSal INTO @Sal
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @STR = @STR + CONVERT(VARCHAR,@Sal) + ', '
FETCH NEXT FROM curSal INTO @Sal
END
CLOSE curSal
DEALLOCATE curSal
SELECT @STR = SUBSTRING (@str, 1, LEN(@str) - 1)
INSERT INTO #2 VALUES (@name, @STR)
FETCH NEXT FROM curNames INTO @Name
END
CLOSE curNames
DEALLOCATE curNames
SELECT * FROM #2
OR You might get rid of One of those cursors and use:
CREATE TABLE #TBL1
(Name VARCHAR(20),
SAl INT)
GO
INSERT INTO #TBL1 VALUES ('Tom', 20)
GO
INSERT INTO #TBL1 VALUES ('Tom', 30)
GO
INSERT INTO #TBL1 VALUES ('TOM', 40)
GO
INSERT INTO #TBL1 VALUES ('Tom', 50)
GO
INSERT INTO #TBL1 VALUES ('Jen', 30)
GO
INSERT INTO #TBL1 VALUES ('Jen', 20)
GO
CREATE TABLE #TBL2
(Name VARCHAR(20),
SAl VARCHAR (300))
GO
DECLARE @Name AS VARCHAR(20), @PrevName varchar (20),
@Sal AS INT,
@str AS VARCHAR(300)
SET @PrevName = ''
DECLARE curNames CURSOR LOCAL FOR
SELECT Name, SAl
FROM #TBL1 Order by Name
OPEN curNames
FETCH NEXT FROM curNames INTO @Name, @Sal
WHILE (@@FETCH_STATUS = 0)
BEGIN
if @PrevName <> @name
Insert Into #TBL2 (Name, SAl) VAlues (@Name, @Sal)
else
UPDATE #TBL2 SET SAL = SAL + ',' + CONVERT(VARCHAR,@Sal) WHERE Name = @Name
SET @PrevName = @Name
FETCH NEXT FROM curNames INTO @Name, @Sal
END
CLOSE curNames
DEALLOCATE curNames
SELECT * FROM #TBL2
DROP TABLE #TBL2
DROP TABLE #TBL1
For the SET BASED You will need The Maximum Of records per name and that still leaves you with a not very pretty query. I personaly think this is one of those cases where cursor (using the right indexes) are a good choice
OR EVEN BETTER:
CREATE TABLE #TBL1
(Name VARCHAR(20),
SAl INT)
GO
INSERT INTO #TBL1 VALUES ('Tom', 20)
GO
INSERT INTO #TBL1 VALUES ('Tom', 30)
GO
INSERT INTO #TBL1 VALUES ('TOM', 40)
GO
INSERT INTO #TBL1 VALUES ('Tom', 50)
GO
INSERT INTO #TBL1 VALUES ('Jen', 30)
GO
INSERT INTO #TBL1 VALUES ('Jen', 20)
GO
CREATE TABLE #TBL2
(Name VARCHAR(20),
SAl VARCHAR (300))
GO
DECLARE @Name AS VARCHAR(20),
@TotSAl AS VARCHAR(1000)
DECLARE curNames CURSOR LOCAL FOR
SELECT DISTINCT [Name]
FROM #TBL1
OPEN curNames
FETCH NEXT FROM curNames INTO @Name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @TotSal = ISNULL(@TotSAl + ',','') + CONVERT(VARCHAR,Sal) FROM #TBL1 WHERE Name = @Name
INSERT INTO #TBL2(Name,SAl) VALUES (@Name, @TotSAl)
FETCH NEXT FROM curNames INTO @Name
END
CLOSE curNames
DEALLOCATE curNames
SELECT * FROM #TBL2
DROP TABLE #TBL2
DROP TABLE #TBL1
* Noel -
Thanks for your help Guys. you are a great help. Happy Holidays
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply