Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CON78788
Starting Member
1 Post |
 Posted - 2010-02-20 : 03:29:53
|
Dear All,
In my tblQuery1, I have the following data:
IsoLbl Comp2 Stencil
PBBCA-PR-16223.01 ELBOW GK14
PBBCA-PR-16223.01 ELBOW GK24
PBBCA-PR-16223.01 ELBOW GK50
PBBCA-PR-16223.01 ELBOW GK55
PBBCA-PR-16223.01 FLANGE GK27
PBBCA-PR-16223.01 FLANGE GK28
In the IsoLbl field as you can see there are 6 identical data. Under the Comp2 field there are 2 types of Iso Components which is the Elbow and the Flange. On the last field there are 4 different Stencils; GK14, GK24, GK50 & GK55. I want these 4 Stencils to be combined in one field since they fall under the same component of ELBOW....so forth and so on.
Report output should look like this:
IsoLbl Comp2 Stencil
PBBCA-PR-16223.01 ELBOW GK14,GK24,GK50,GK55
PBBCA-PR-16223.01 FLANGE GK27,GK28
I tried using the Grouping but no success.
Your help is highly appreciated. Thanks.
CON78788 |
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-02-20 : 05:30:31
|
This might be your solution
-----
DECLARE @temp TABLE ( IsoLbl VARCHAR(100), Comp2 VARCHAR(20), Stencil VARCHAR(20) )
INSERT INTO @temp
SELECT 'PBBCA-PR-16223.01', 'ELBOW', 'GK14'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'ELBOW', 'GK24'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'ELBOW', 'GK50'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'ELBOW', 'GK55'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'FLANGE', 'GK27'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'FLANGE', 'GK28'
DECLARE @ROWCOUNT BIGINT
DECLARE @Stencil VARCHAR(MAX)
DECLARE @C VARCHAR(20)
DECLARE @Iso VARCHAR(100)
DECLARE @TAB TABLE (IsoLbl VARCHAR(100), Comp2 VARCHAR(20))
DECLARE @TAB1 TABLE (IsoLbl VARCHAR(100), Comp2 VARCHAR(20),Stencil VARCHAR(MAX))
INSERT INTO @TAB
SELECT DISTINCT IsoLbl , Comp2
FROM @temp
SET @ROWCOUNT = @@ROWCOUNT
WHILE @ROWCOUNT>0
BEGIN
SET @Stencil = ''
SELECT TOP 1 @Iso=IsoLbl, @C=Comp2 FROM @TAB ORDER BY Comp2
SELECT @Stencil =@Stencil+',' + Stencil
FROM (
SELECT Stencil FROM @Temp
WHERE Comp2 = @c
) X
INSERT INTO @TAB1 (isolbl,comp2,stencil) VALUES(@iso,@C,SUBSTRING(@Stencil ,2,LEN(@Stencil )) )
DELETE A FROM @TAB A
INNER JOIN @TAB1 B ON B.Comp2=A.Comp2
SET @ROWCOUNT =@ROWCOUNT-@@ROWCOUNT
END
SELECT * FROM @TAB1 WHERE Stencil <>''
-------
Refer your main table tblQuery1 instead of @temp table
Vabhav T |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-20 : 05:47:59
|
quote:
Originally posted by vaibhavktiwari83
This might be your solution
-----
DECLARE @temp TABLE ( IsoLbl VARCHAR(100), Comp2 VARCHAR(20), Stencil VARCHAR(20) )
INSERT INTO @temp
SELECT 'PBBCA-PR-16223.01', 'ELBOW', 'GK14'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'ELBOW', 'GK24'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'ELBOW', 'GK50'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'ELBOW', 'GK55'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'FLANGE', 'GK27'
UNION ALL
SELECT 'PBBCA-PR-16223.01', 'FLANGE', 'GK28'
DECLARE @ROWCOUNT BIGINT
DECLARE @Stencil VARCHAR(MAX)
DECLARE @C VARCHAR(20)
DECLARE @Iso VARCHAR(100)
DECLARE @TAB TABLE (IsoLbl VARCHAR(100), Comp2 VARCHAR(20))
DECLARE @TAB1 TABLE (IsoLbl VARCHAR(100), Comp2 VARCHAR(20),Stencil VARCHAR(MAX))
INSERT INTO @TAB
SELECT DISTINCT IsoLbl , Comp2
FROM @temp
SET @ROWCOUNT = @@ROWCOUNT
WHILE @ROWCOUNT>0
BEGIN
SET @Stencil = ''
SELECT TOP 1 @Iso=IsoLbl, @C=Comp2 FROM @TAB ORDER BY Comp2
SELECT @Stencil =@Stencil+',' + Stencil
FROM (
SELECT Stencil FROM @Temp
WHERE Comp2 = @c
) X
INSERT INTO @TAB1 (isolbl,comp2,stencil) VALUES(@iso,@C,SUBSTRING(@Stencil ,2,LEN(@Stencil )) )
DELETE A FROM @TAB A
INNER JOIN @TAB1 B ON B.Comp2=A.Comp2
SET @ROWCOUNT =@ROWCOUNT-@@ROWCOUNT
END
SELECT * FROM @TAB1 WHERE Stencil <>''
-------
Refer your main table tblQuery1 instead of @temp table
Vabhav T
The question is posted at MS Access forum in which your solution will not work
Madhivanan
Failing to plan is Planning to fail |
|
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-02-20 : 06:37:29
|
Oh really i missed sorry i cant help in ms access
but if you can convert it then well and good
Vabhav T |
|
|
|
|
|
|
|
|
Combine multiple row data into 1