Solved

concat one column from several rows

Posted on 2013-06-07
12
317 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now