Solved

concat one column from several rows

Posted on 2013-06-07
12
314 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Brilliant- thanks very much.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.

744 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

18 Experts available now in Live!

Get 1:1 Help Now