Combine multiple row data into 1

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.

All Forums

Other Forums

MS Access

Combine multiple row data into 1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources