Join the two table along with concatenation using key

Question

I am able to join the two tables and get the field called TextLine. But Textline has multiple values for one key want to group them into one line per key I already could match the 2 tables using the join. But I have multiple lines due to Textline, want to have that text line multiple lines into one using the key used for joining two table

    <pre>
         SELECT TOP (100) PERCENT 
          dbo.POHeader.PoNo
           , dbo.POHeader.ShipDate
            , dbo.POHeader.Style, dbo.POHeader.StyleDesc
              ,dbo.POHeader.Quota,
        (CONCAT(RTRIM(LTRIM(dbo.POHeader.QuotaName)),RTRIM(LTRIM(dbo.POHeader.QuotaName2)),RTRIM(LTRIM(dbo.POHeader.QuotaName3)),RTRIM(LTRIM(dbo.POHeader.QuotaName4)),RTRIM(LTRIM(dbo.POHeader.QuotaName5)),RTRIM(LTRIM(dbo.POHeader.QuotaName6)))) as QuotaName
        , dbo.POHeader.VendorName
        , dbo.POHeader.CountryofOrigin
        , dbo.[PODetail-TotalPcs-TotalCost].TotalPcs
        , dbo.POHeader.HTS
        , dbo.poheader.DC
        ,dbo.POHeader.DeliveryDate
        , dbo.[POText-T].TextLine 

        FROM dbo.POHeader INNER JOIN dbo.[PODetail-TotalPcs-TotalCost] ON dbo.POHeader.PoNo = dbo.[PODetail-TotalPcs-TotalCost].PoNo

        LEFT JOIN dbo.[POText-T] ON dbo.POHeader.PoNo = dbo.[POText-T].PoNo 

        WHERE  dbo.POHeader.DeliveryDate >= '11/01/19' AND dbo.POHeader.DeliveryDate <= '11/30/19' AND dbo.POHeader.DC IN ('W2WM','W2WJ')'</pre>

###Current table:###
#PoNO | SHIPDATE | TEXTLine#
##548756 | 3/4/2018 | BOYS##
##548756 | 3/4/2018 |  SHOES##

#Required ans:#
##PoNO | SHIPDATE |TextLine##
##548756 |3/4/2018 | Boys Shoes##

Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — alexherm, Oct 17 '19 at 22:45

score 0 · Answer 1 · answered Oct 17 '19 at 23:27

If you are looking for rows into columns in sql server then 1 way to do it is using XML

WITH Current_table AS(
SELECT 548756 PoNO , CAST('3/4/2018' AS datetime) SHIPDATE ,'BOYS' TEXTLine union all
SELECT 548756  , CAST('3/4/2018' AS datetime),'SHOES'  union all
SELECT 548756  , CAST('3/5/2018' AS datetime),'SHOES'
)
 SELECT PoNO
        ,SHIPDATE
        ,STUFF((SELECT ' '+TEXTLine 
                FROM Current_table a 
                WHERE a.SHIPDATE=b.SHIPDATE 
                AND a.PoNO=b.PoNO FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') TEXTLine
 FROM Current_table b
 GROUP BY SHIPDATE,PoNO

Output :

PoNO    SHIPDATE                TEXTLine
548756  2018-03-04 00:00:00.000 BOYS SHOES
548756  2018-03-05 00:00:00.000 SHOES

Join the two table along with concatenation using key

1 Answers1