Solved

concat one column from several rows

Posted on 2013-06-07
12
327 Views
Last Modified: 2013-06-07
One query returns 15 columns. 13 are the same result. the remaining 2 can be different..

Those 2 columns will be the Code and the Description. They always corelate. Code 5 is always 'apple' and Code 6 is always organge and so forth.

Is it possible to concat the columns 14 & 15, and attach with the distinct of the remaining 13 columns.

for example
row1: ......13 columns.........., '05','apples'
row2: ......13 columns.........., '07','mango'
row3: ......13 columns.........., '09','Plumns'
/*the 13 columns are the same values for the above 3 rows*/
row4: ......13 columns.........., '05','apples'
row5: ......13 columns.........., '06','Oranges'
/*the 13 columns are the same values for the above 2 rows*/
row6: ......13 columns.........., '01','Kiwi'
row7: ......13 columns.........., '11','BlueBerry'
row8: ......13 columns.........., '04','pine'
/*the 13 columns are the same values for the above 3 rows*/

the results we would prefer is 3 rows instead of the 8 above, as below:
.....13 columns.........., '05 apples-07 mango-09 plums'
.....13 columns.........., '05 apples-06 Oranges'
.....13 columns.........., '01 Kiwi-11 BlueBerry-04 pine'
0
Comment
Question by:25112
12 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39228993
you can do this way.

select * 
into #t2
from(
select '1'num,'abc'textdata,7450 amt, 10 qty, 'apple'fruit
union
select '1'num,'abc'textdata,7450 amt, 10 qty, 'apple'fruit
union
select '1'num,'abc'textdata,7450 amt, 10 qty, 'apple'fruit
union
select '1'num,'abc'textdata,7450 amt, 12 qty, 'grapes'fruit
union
select '1'num,'abc'textdata,7450 amt, 13 qty, 'banana'fruit
)a

select * from #t2
group by num,textdata,amt,qty,fruit

Open in new window

0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39229000
hi  25112,

You can use for xml to do what you want.

i created this rough example as below

if you give the full details of you table i'll knock up something better.

http://sqlfiddle.com/#!3/9917a/7

SELECT  distinct
         ID,

         STUFF(
               (SELECT      ',' + cast(SubTableUser.codes as varchar(30)) + '-' + SubTableUser.Name
               FROM      TEST AS SubTableUser
               WHERE      SubTableUser.ID = TEST.ID
               FOR XML PATH('')), 1, 1, '') AS Products

FROM      TEST

Open in new window


Create Table Test(
ID int,Codes int  ,Name varchar(30))

INSERT INTO TEST(ID ,Codes ,Name) VALUES('1','5','apples');
INSERT INTO TEST(ID ,Codes ,Name) VALUES('1','7','mango');
INSERT INTO TEST(ID ,Codes ,Name) VALUES('1','9','Plumns');
INSERT INTO TEST(ID ,Codes ,Name) VALUES('2','5','apples');
INSERT INTO TEST(ID ,Codes ,Name) VALUES('2','6','Oranges');
INSERT INTO TEST(ID ,Codes ,Name) VALUES('3','1','Kiwi');
INSERT INTO TEST(ID ,Codes ,Name) VALUES('3','11','BlueBerry');
INSERT INTO TEST(ID ,Codes ,Name) VALUES('3','4','pine');

Open in new window

0
 
LVL 5

Author Comment

by:25112
ID: 39229080
RossTurner, this is the actual query.


SELECT
      i.IC_BasketFK                               AS IC_baskFK,
      l.AppSOSBasketFK                               AS SOSbaskFK,
      CONVERT(VARCHAR(10),l.OriginalApprovalIssuedDate,101)       AS AppIssuedDt,
      CONVERT(VARCHAR(10),l.AppEffectiveFromDate,101)       AS AppEffectDt,
      CONVERT(VARCHAR(10),l.AppExpirationDate,101)             AS AppExpDt,
      lc.AppCodeCodeFK                               AS AppCodeCodeFK,
      lcc.AppCodeDesc                               AS AppCodeDesc,
      ISNULL(il.baskCollectLevelCodeFK,0)                               AS baskLvlCodeFK,
      ISNULL(ilc.baskCollectLevelDesc,'')                               AS baskLvlDesc,
      CONVERT(VARCHAR(10),lc.AppCodeStartDate,101)            AS CodeStDt,
      CASE ISNULL(baskCollectLevelStartDate,'')
            WHEN '' THEN ''
      ELSE CONVERT(VARCHAR(10),il.baskCollectLevelStartDate,101)       
      END As baskStDt,
      ISNULL(ile.AppExceptionalityCodeFK,0)                               AS ExceptCodeFK,
      ISNULL(le.AppExceptionalityDesc,'')                               AS ExceptDesc,
      isnull(AppPrintDate,'01/01/2079') AppPrintDate,
      case when isnull(AppPrintDate,'01/01/2014') < CONVERT(VARCHAR(10),lc.AppCodeStartDate,101) then 1
            else 0
      end baskCodeAddedInd
FROM
      dbo.tblApproval l

      INNER JOIN dbo.tblSOSBasket i on
      i.SOSBasketPK = l.AppSOSBasketFK

      INNER JOIN dbo.tblbaskAppCodeHistory lc on
      lc.AppSOSBasketFK = l.AppSOSBasketFK
      AND      lc.AppCodeEndDate is null

      INNER JOIN dbo.tblAppCodeCode lcc on
      lcc.AppCodeCodePK = lc.AppCodeCodeFK

      LEFT OUTER JOIN dbo.tblbaskAppbaskCollectLevelHistory il on
      lc.baskAppCodeHistoryPK = il.baskAppCodeHistoryFK
      AND      il.baskCollectLevelEndDate is null

      LEFT OUTER JOIN dbo.tblbaskCollectLevelCode ilc on
      ilc.baskCollectLevelCodePK = il.baskCollectLevelCodeFK
      AND      ilc.AppCodeCodeFK = lc.AppCodeCodeFK

      LEFT OUTER JOIN dbo.tblbaskAppExceptionality ile on
      ile.baskAppCodeHistoryFK = lc.baskAppCodeHistoryPK
      
      LEFT OUTER JOIN dbo.tblAppExceptionalityCode le on
      ile.AppExceptionalityCodeFK = le.AppExceptionalityCodePK

WHERE
      i.IC_BasketFK             = 52803

order by
      l.AppSOSBasketFK,
      l.OriginalApprovalIssuedDate,
      lc.AppCodeCodeFK,
      baskLvlCodeFK

  ISNULL(il.baskCollectLevelCodeFK,0)                               AS baskLvlCodeFK,
      ISNULL(ilc.baskCollectLevelDesc,'')                               AS baskLvlDesc,

are the 2 columns we want concatinated.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Expert Comment

by:Ross Turner
ID: 39229113
Whoa.... it a big one. i should have said a an example of the data.

From that i will be able to work out the id to join them back on as in my example i used id.

ID       Codes       Name
1      5      apples
1      7      mango
1      9      Plumns
2      5      apples
2      6      Oranges
3      1      Kiwi
3      11      BlueBerry
3      4      pine

what defines them to that line ?
0
 
LVL 5

Author Comment

by:25112
ID: 39229233
i am afraid there is no id.. (the 13 columns will make the id - unique value)

also lc.AppCodeCodeFK                               AS AppCodeCodeFK,
      lcc.AppCodeDesc                               AS AppCodeDesc, holds the Code and Fruit Name.

  ISNULL(il.baskCollectLevelCodeFK,0)                               AS baskLvlCodeFK,
      ISNULL(ilc.baskCollectLevelDesc,'')                               AS baskLvlDesc,
is what  need concatenated.

please see attached diagram to see the data example.

thanks again.
Untitled.png
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39229262
Hi 25112

I knocked up another example on sql fiddle using your column names.

obviously i stuck in the data already mentioned the other stuff i just left as null.

(Select statement stylee)
http://sqlfiddle.com/#!3/8a5f4/14

or

(CTE Statement stylee )
http://sqlfiddle.com/#!3/8a5f4/19

have a go :0
0
 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 250 total points
ID: 39229324
Right here we go again.....lol

i got confused with your headers but this is it bascally

http://sqlfiddle.com/#!3/f11a3/9

take a look
0
 
LVL 5

Author Comment

by:25112
ID: 39229501
looks great- it works well.. thanks a lot.

one question: in the above example, the other 13 columns were identical..  now i saw some cases where one of two columns differed..

the solution works for this also.. but the concatanation repeats itself sometimes..

is it possible to implement the above logic only after checking if all the rest of the 13 columns are identical?

for example, see code below:

Create Table TEST

(
            Column1 int,
            Column2 int ,
            Column3 varchar(30),
            Column4 varchar(30),
            Column5 varchar(30),
            Column6 int,
            Column7 varchar(30),
            Column8 int,
            Column9 varchar(30),
            Column10 varchar(30),
            Column11 varchar(30),
            Column12 int,
            Column13 varchar(30),
            Column14 int
            )

INSERT INTO TEST(Column1 ,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14) VALUES('5280','195','02/18/2006','02/18/2012','02/18/2013','5','APPLES','1','Bushel','07/17/2006','07/17/2006','0','08/11/2012 08:03:00','0');
INSERT INTO TEST(Column1 ,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14) VALUES('5280','195','02/18/2006','02/18/2012','02/18/2013','5','APPLES','2','Truck ','07/17/2006','07/17/2006','0','08/11/2012 08:03:00','0');
INSERT INTO TEST(Column1 ,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14) VALUES('5281','195','02/18/2006','02/18/2012','02/18/2013','5','APPLES','4','Retail ','07/17/2006','07/17/2006','0','08/11/2012 08:03:00','0');

select * from test;


Select distinct
Column1,
            Column2,
            Column3,
            Column4,
            Column5,
            Column6,
            Column7,
            Column10,
            Column11,
            Column12,
            Column13,
            Column14 ,
STUFF((
                  SELECT ',' + cast(SubTableUser.COLUMN8 AS VARCHAR(30)) + '-' + SubTableUser.COLUMN9
                  FROM TEST AS SubTableUser
                  WHERE SubTableUser.COLUMN6 = TEST.COLUMN6
                  FOR XML PATH('')
                  ), 1, 1, '') AS CONCAT_RESULT
from TEST
0
 
LVL 5

Author Comment

by:25112
ID: 39229594
i changed the column1 value differently.. so this one should have just got 4-Retail, but now gets 1-Bushel,2-Truck, 4-Retail

sorry i should have seen this in the beginning and explained it...
0
 
LVL 5

Author Comment

by:25112
ID: 39229601
the ideal condition is below and works beautifully.. but in an unideal condition, can we tweak the query to accommodate that?

Create Table TEST

(
            Column1 int,
            Column2 int ,
            Column3 varchar(30),
            Column4 varchar(30),
            Column5 varchar(30),
            Column6 int,
            Column7 varchar(30),
            Column8 int,
            Column9 varchar(30),
            Column10 varchar(30),
            Column11 varchar(30),
            Column12 int,
            Column13 varchar(30),
            Column14 int
            )

INSERT INTO TEST(Column1 ,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14) VALUES('5280','195','02/18/2006','02/18/2012','02/18/2013','5','APPLES','1','Bushel','07/17/2006','07/17/2006','0','08/11/2012 08:03:00','0');
INSERT INTO TEST(Column1 ,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14) VALUES('5280','195','02/18/2006','02/18/2012','02/18/2013','5','APPLES','2','Truck ','07/17/2006','07/17/2006','0','08/11/2012 08:03:00','0');
INSERT INTO TEST(Column1 ,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14) VALUES('5280','195','02/18/2006','02/18/2012','02/18/2013','5','APPLES','4','Retail ','07/17/2006','07/17/2006','0','08/11/2012 08:03:00','0');

select * from test;


Select distinct
Column1,
            Column2,
            Column3,
            Column4,
            Column5,
            Column6,
            Column7,
            Column10,
            Column11,
            Column12,
            Column13,
            Column14 ,
STUFF((
                  SELECT ',' + cast(SubTableUser.COLUMN8 AS VARCHAR(30)) + '-' + SubTableUser.COLUMN9
                  FROM TEST AS SubTableUser
                  WHERE SubTableUser.COLUMN6 = TEST.COLUMN6
                  FOR XML PATH('')
                  ), 1, 1, '') AS CONCAT_RESULT
from TEST

drop table TEST
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 39229812
try this.
;WITH cte 
     AS (SELECT *, 
                DENSE_RANK() 
                  OVER ( 
                    ORDER BY Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column10, Column11, Column12, Column13, Column14) rn
         FROM   Test) 
SELECT DISTINCT Column1, 
                Column2, 
                Column3, 
                Column4, 
                Column5, 
                Column6, 
                Column7, 
                Column10, 
                Column11, 
                Column12, 
                Column13, 
                Column14, 
                STUFF((SELECT ',' 
                              + CAST(SubTableUser.COLUMN8 AS VARCHAR(30)) 
                              + '-' + SubTableUser.COLUMN9 
                       FROM   cte AS SubTableUser 
                       WHERE  SubTableUser.rn = cte.rn 
                       FOR XML PATH('')), 1, 1, '') AS CONCAT_RESULT 
FROM   cte;

Open in new window


http://sqlfiddle.com/#!3/bca43/7
0
 
LVL 5

Author Comment

by:25112
ID: 39229887
Brilliant- thanks very much.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question