Concatenate distinct column 1 with none distinct countepart in column 2

[appleglaze28]

I'd like to ask a lil help from everyone. I have this problem. I have like items in column 1 & 2:

COLUMN 1 COLUMN 2
0001 0043
0001 0056
0001 0078
0002 0043

I just need 1 row per distinct item in column 1 but I need the items in column 2. So I thought is it possible to like concatenate all 3 distinct number in column 2 with the same item in column 1?

I can't figure out what statement to write to get the result I need.

[sknake]

There are a few solutions. If you know you will only need to ever concat up to, say, 6 values then you could hardcode a solution:

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
Create Table #Table
(
  Column1 varchar(4),
  Column2 varchar(4)
)

Insert Into #Table (Column1, Column2) Values ('0001', '0043')
Insert Into #Table (Column1, Column2) Values ('0001', '0056')
Insert Into #Table (Column1, Column2) Values ('0001', '0078')
Insert Into #Table (Column1, Column2) Values ('0001', '0078') --Dupe value as above
Insert Into #Table (Column1, Column2) Values ('0002', '0043')
Insert Into #Table (Column1, Column2) Values ('0003', '0043')
Insert Into #Table (Column1, Column2) Values ('0003', '0056')
Insert Into #Table (Column1, Column2) Values ('0003', '0078')
Insert Into #Table (Column1, Column2) Values ('0003', '0011')
Insert Into #Table (Column1, Column2) Values ('0003', '0015')
Insert Into #Table (Column1, Column2) Values ('0004', '0043')

Select 
Column1,
(
  MAX(Case When RowNum = 1 Then Column2 Else '' End) +
  MAX(Case When RowNum = 2 Then '-' + Column2 Else '' End) +
  MAX(Case When RowNum = 3 Then '-' + Column2 Else '' End) +
  MAX(Case When RowNum = 4 Then '-' + Column2 Else '' End) +
  MAX(Case When RowNum = 5 Then '-' + Column2 Else '' End) +
  MAX(Case When RowNum = 6 Then '-' + Column2 Else '' End) 
) 
As c2
From
(
  Select Column1, Column2, ROW_NUMBER() over (PARTITION BY Column1 Order By Column2) As RowNum
  From #Table
  Group By Column1, Column2
) as tbl
Group By Column1
Order By Column1

Unfortunately that does have a ceiling. Here is a way to get around that but I don't recommend using it. You can find plenty of literature on why using cursors in SQL is bad so i'll leave that up to you. Here is another way to do it using cursors (not recommended):

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
Create Table #Table
(
  Column1 varchar(4),
  Column2 varchar(4)
)

Insert Into #Table (Column1, Column2) Values ('0001', '0043')
Insert Into #Table (Column1, Column2) Values ('0001', '0056')
Insert Into #Table (Column1, Column2) Values ('0001', '0078')
Insert Into #Table (Column1, Column2) Values ('0001', '0078') --Dupe value as above
Insert Into #Table (Column1, Column2) Values ('0002', '0043')
Insert Into #Table (Column1, Column2) Values ('0003', '0043')
Insert Into #Table (Column1, Column2) Values ('0003', '0056')
Insert Into #Table (Column1, Column2) Values ('0003', '0078')
Insert Into #Table (Column1, Column2) Values ('0003', '0011')
Insert Into #Table (Column1, Column2) Values ('0003', '0015')
Insert Into #Table (Column1, Column2) Values ('0004', '0043')

Declare @Tab Table
(
  Column1 varchar(4) PRIMARY KEY,
  Column2 varchar(6000)
)


Declare @a varchar(4), @result varchar(6000)

DECLARE det_cursor CURSOR FOR Select Distinct(Column1) From #Table

OPEN det_cursor
FETCH NEXT FROM det_cursor INTO @a

WHILE (@@FETCH_STATUS = 0)
BEGIN
  Set @result = NULL
  Select @result = IsNull(@result+'-', '') + IsNull(Column2, '') From #Table Where #Table.Column1 = @a
  Insert Into @Tab (Column1, Column2) Values (@a, @result)
  FETCH NEXT FROM det_cursor INTO @a
END


CLOSE det_cursor
DEALLOCATE det_cursor

Select *
From @Tab

[jbisono]

Here is two ways that you can accomplish this query, refer to this thread: http://www.daniweb.com/forums/thread190891.html

Regards.

[appleglaze28]

Well I'm quiet new at this so I'm not really sure what part to alter or your first approach is the best way to like do what I want to do. Since in my data need to query 6 columns and I'm linking 2 tables before I concatenate the items. I just tested the 2 columns first.

SELECT
vue_ar.doccode,
(
  MAX(Case When RowNum = 1 Then vue_sales.sono Else '' End) +
  MAX(Case When RowNum = 2 Then '-' + vue_sales.sono Else '' End) +
  MAX(Case When RowNum = 3 Then '-' + vue_sales.sono Else '' End) +
  MAX(Case When RowNum = 4 Then '-' + vue_sales.sono Else '' End) +
  MAX(Case When RowNum = 5 Then '-' + vue_sales.sono Else '' End) +
  MAX(Case When RowNum = 6 Then '-' + vue_sales.sono Else '' End) 
) 
As sono
From
(
  Select vue_ar.doccode, vue_sales.sono, ROW_NUMBER() over (PARTITION BY vue_ar.doccode Order By vue_sales.sono) As RowNum,
LEFT(vue_ar.doccode,2) +(CASE 
WHEN left(vue_ar.doccode,2) LIKE 'IN%'
THEN 
(CASE 
WHEN LEN(vue_ar.doccode) = 7
THEN '0'+RIGHT(vue_ar.doccode,6)
ELSE RIGHT(vue_ar.doccode,6)
END)
WHEN left(vue_ar.doccode,2) LIKE 'DR%'
THEN 
(CASE 
WHEN LEN(vue_ar.doccode) = 7
THEN '0'+RIGHT(vue_ar.doccode,6)
ELSE RIGHT(vue_ar.doccode,6)
END)
ELSE RIGHT(vue_ar.doccode,6)
END)as doccode_6Digit,
LEFT(vue_ar.doccode,2) +(CASE 
WHEN left(vue_ar.doccode,2) LIKE 'IN%'
THEN 
(CASE 
WHEN LEN(vue_ar.doccode) = 7	
THEN RIGHT(vue_ar.doccode,6)
ELSE RIGHT(vue_ar.doccode,5)
END)
WHEN LEFT(vue_ar.doccode,2) LIKE 'DR%'
THEN 
(CASE 
WHEN LEN(vue_ar.doccode) = 7
THEN RIGHT(vue_ar.doccode,6)
ELSE RIGHT(vue_ar.doccode,5)
END)
ELSE RIGHT(vue_ar.doccode,6)
END)as doccode_num
FROM vue_ar, dim_client, vue_sales 
WHERE dim_client.client_caption=vue_ar.client_caption
AND vue_ar.doccode not like '%[A-Z]'
AND LEFT(vue_ar.doccode,2) +(CASE 
WHEN left(vue_ar.doccode,2) LIKE 'IN%'
THEN 
(CASE 
WHEN LEN(vue_ar.doccode) = 7
THEN '0'+RIGHT(vue_ar.doccode,6)
ELSE RIGHT(vue_ar.doccode,6)
END)
WHEN left(vue_ar.doccode,2) LIKE 'DR%'
THEN 
(CASE 
WHEN LEN(vue_ar.doccode) = 7
THEN '0'+RIGHT(vue_ar.doccode,6)
ELSE RIGHT(vue_ar.doccode,6)
END)
ELSE RIGHT(vue_ar.doccode,6)
END)= LEFT(vue_sales.doccode,2) +(CASE 
WHEN left(vue_sales.doccode,2) LIKE 'IN%'
THEN 
(CASE 
WHEN LEN(vue_sales.doccode) = 7
THEN '0'+RIGHT(vue_sales.doccode,6)
ELSE RIGHT(vue_sales.doccode,6)
END)
WHEN left(vue_sales.doccode,2) LIKE 'DR%'
THEN 
(CASE 
WHEN LEN(vue_sales.doccode) = 7
THEN '0'+RIGHT(vue_sales.doccode,6)
ELSE RIGHT(vue_sales.doccode,6)
END)
ELSE RIGHT(vue_sales.doccode,6)
END)

Its kinda a bit hard for me to spot my mistake in this cause I'm referencing 2 tables.

[sknake]

Perhaps if you included the error message? I can see you have 2 FROM statements that is probably incorrect. Can you post the create table statements for the tables involved on your end?

[appleglaze28]

Well my access to the database is restricted so I'm not allowed to create tables I just need to get that output.

I'm fairly new to MS SQL so I'm still trying to get used to the other syntax

[sknake]

Can you post the error message?

[appleglaze28]

The error message i got isMsg 4104, Level 16, State 1, Line 18
The multi-part identifier "nxs_bi.dbo.rel_docsoprod.doccode" could not be bound.
Msg 344, Level 16, State 1, Line 2
Remote function reference 'nxs_bi.dbo.rel_docsoprod.doccode' is not allowed, and the column name 'nxs_bi' could not be found or is ambiguous.
'FROM'

I'm still trying to test this out cause I might need to as well create a query that will do the same for column 3. So the number of rows must be equivalent to the number of distinct item on column 1. Could you help me out on how to make it worth a simple sample for query. Or is it more adviseable to transfer all data into a view or 1 single table?