Join rows from the same table

1

Hi,

I have this table:

PARTNAME PARTDES Column1
1050 TAL I want to buy a chair some text
1050 TAL text 123
11111 IRIS whats up with you hellow there
11111 IRIS I want ice cream

I want to see it like this:

PARTNAME PARTDES Column1
1050 TAL I want to buy a chair some text, text 123
11111 IRIS whats up with you hellow there , I want ice cream

is it possible? I'm running on sql management studio v 14.0.

this is the code that creates the first table.

SELECT PART.PART, PART.PARTNAME, PART.PARTDES,
REVERSE(replace(replace(replace(replace(replace(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PARTTEXT.TEXT, 'il,vid,p >elyts<', ''),
'ylimaf-tnof;tp0.01:ezis-tnof;mc0:nigram{', ''),
'lairA', ''), 'P< >elyts/<};', ''), ''':', ''), '>P/<;psbn&', ''), '>P/<>RB<', ''), '>ltr=rid', ''), '''', ''), '<', ''),
'>', ''), 'P', ''), '/', ''))
FROM PART, PARTTEXT
WHERE PART.PART = PARTTEXT.PART
AND PART.PARTNAME IN('1050', '11111')
AND REVERSE(replace(replace(replace(replace(replace(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PARTTEXT.TEXT, 'il,vid,p >elyts<', ''),
'ylimaf-tnof;tp0.01:ezis-tnof;mc0:nigram{', ''),
'lairA', ''), 'P< >elyts/<};', ''), ''':', ''), '>P/<;psbn&', ''), '>P/<>RB<', ''), '>ltr=rid', ''), '''', ''), '<', ''),
'>', ''), 'P', ''), '/', '')) <> ''

2
Create the sample data 
DROP TABLE IF EXISTS dbo.DaTable;
GO

CREATE TABLE dbo.DaTable (
	id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,PARTNAME VARCHAR(10) NOT NULL
	,PARTDES VARCHAR(10) NOT NULL
	,Column1 VARCHAR(255) NOT NULL
	);
GO

INSERT INTO dbo.DaTable (
	PARTNAME
	,PARTDES
	,Column1
	)
VALUES 
('1050','TAL','I want to buy a chair some text')
,('1050','TAL','text 123')
,('11111','IRIS','whats up with you hellow there')
,('11111','IRIS','I want ice cream');

If you're using SQL Server 2017 and up

SQL script 
;WITH group_CTE
AS (
	SELECT DISTINCT PARTNAME
		,PARTDES
	FROM dbo.DaTable
	)
SELECT group_CTE.PARTNAME
	,group_CTE.PARTDES
	,Column1 = STRING_AGG(DT.Column1, ', ') 
		WITHIN GROUP (ORDER BY DT.id)
FROM group_CTE
INNER JOIN dbo.DaTable AS DT
	ON group_CTE.PARTNAME = DT.PARTNAME
		AND group_CTE.PARTDES = DT.PARTDES
GROUP BY group_CTE.PARTNAME
	,group_CTE.PARTDES
ORDER BY group_CTE.PARTNAME
	,group_CTE.PARTDES;

If you're using SQL Server 2016 or lower

SQL script 
;WITH group_CTE
AS (
	SELECT DISTINCT PARTNAME
		,PARTDES
	FROM dbo.DaTable
	)
SELECT group_CTE.PARTNAME
	,group_CTE.PARTDES
	,Column1 = STUFF((SELECT ', ' + DT.Column1 
			FROM dbo.DaTable AS DT 
			WHERE group_CTE.PARTNAME = DT.PARTNAME
				AND group_CTE.PARTDES = DT.PARTDES
			ORDER BY DT.id
			FOR XML PATH('')
			), 1, 2, N''
					)
FROM group_CTE
ORDER BY group_CTE.PARTNAME
	,group_CTE.PARTDES;

You may have noticed the
ORDER BY DT.id
part.

You probably want "I want to buy a chair some text, text 123" in the result over and over again. If you don't put in an ORDER clause you may get "text 123, I want to buy a chair some text" in the result from time to time.
Most of the time it's easy to find a column to order by, a timestamp, or something else that makes sense to your business.

1 Like
3

Join rows from the same table
SQL> create table ecom_page_meta (post_id number not null
2 , meta_name varchar2(15) not null
3 , meta_value varchar2(15) not null);

Table created.
SQL> insert into ecom_page_meta values (1, 'bar', '44');
1 row created.
SQL> insert into ecom_page_meta values (2, 'bar', '1');
1 row created.
SQL> insert into ecom_page_meta values (2, 'foo', 'on');
1 row created.
SQL> insert into ecom_page_meta values (3, 'foo', 'off');
1 row created.
SQL> commit;
SQL> insert into ecom_page_meta values (1, 'foo', 'on');
1row created.
SQL> insert into ecom_page_meta values (3, 'bar', '1');
1 row created.
Commit complete.

4

hi

i tried to do this !!!!

Hope this helps !!!! :slight_smile:

please click arrow to the left for DROP Create Data .. 
drop table #data
go 

create table #data 
(
PARTNAME	int,
PARTDES	    varchar(100),
Column1	    varchar(100)
)
go 

insert into #data select 1050	,'TAL','I want to buy a chair some text'
insert into #data select 1050	,'TAL','text 123'
insert into #data select 11111	,'IRIS','whats up with you hellow there'
insert into #data select 11111	,'IRIS','I want ice cream'
go 

select 'data',* from #data
go

image

SELECT   'SQL Output'
       , t.PARTNAME
       , t.PARTDES
	   , stuff((SELECT ','+ Column1 FROM #data  WHERE PARTNAME = t.PARTNAME AND PARTDES = t.PARTDES  FOR XML PATH('') ),1,1,'') 
FROM 
    (SELECT Distinct PARTNAME,PARTDES FROM #data ) t

image