Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

concat one column from several rows

Posted on 2013-06-07
12
Medium Priority
?
341 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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 1000 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 41

Accepted Solution

by:
Sharath earned 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 Backup & Restore 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.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

886 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