October 7, 2014 at 4:26 am
DDL Script
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_Data]') AND type in (N'U'))
DROP TABLE [dbo].[Test_Data]
GO
CREATE TABLE [Test_Data](
[Test_key] [int] NOT NULL,
[Test_prog_type_key] [int] NOT NULL,
)
insert into Test_Data values (4828624,3)
insert into Test_Data values (4828624,5)
insert into Test_Data values (615068,15)
insert into Test_Data values (615068,2)
-------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ABC_Data]') AND type in (N'U'))
DROP TABLE [dbo].[ABC_Data]
GO
CREATE TABLE [ABC_Data](
[ABC_key] [int] NOT NULL,
[ABC_prog_type] NVARCHAR(50) null
)
INSERT INTO ABC_Data VALUES (-1,'Unknown')
INSERT INTO ABC_Data VALUES(0,'NA')
INSERT INTO ABC_Data VALUES(1,'Sachin')
INSERT INTO ABC_Data VALUES(2,'Rahul')
INSERT INTO ABC_Data VALUES(3,'Dinesh')
INSERT INTO ABC_Data VALUES(4,'Member')
INSERT INTO ABC_Data VALUES(5,'New York')
INSERT INTO ABC_Data VALUES(6,'London')
INSERT INTO ABC_Data VALUES(7,'Delhi')
INSERT INTO ABC_Data VALUES(8,'Mumbai')
INSERT INTO ABC_Data VALUES(9,'Sydney')
INSERT INTO ABC_Data VALUES(10,'Tom')
INSERT INTO ABC_Data VALUES(11,'Youth')
INSERT INTO ABC_Data VALUES(12,'Manoj')
INSERT INTO ABC_Data VALUES(13,'Scott')
INSERT INTO ABC_Data VALUES(14,'King')
INSERT INTO ABC_Data VALUES(15,'Tree')
INSERT INTO ABC_Data VALUES(16,'Family')
-------------------------------------------------
My SQL
WITH q
AS (
SELECT [Test_key]
,[ABC_key]
FROM [Test_Data]
JOIN [ABC_Data] ON [Test_prog_type_key] = [ABC_key]
)
SELECT DISTINCT q.[Test_key]
,[ABC_Type] = STUFF((
SELECT ', ' + pt.[ABC_prog_type]
FROM [ABC_Data] pt
JOIN q q2 ON q2.[ABC_key] = pt.[ABC_key]
WHERE q.[Test_key] = q2.[Test_key]
FOR XML PATH('')
), 1, 1, '')
FROM q
i have created above sql for concatenates records.
but i think its simple but not the efficient methode.
So request you can some budy help me to provide the sql better then mine?
October 7, 2014 at 4:37 am
Why do you think it is not efficient?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 7, 2014 at 4:41 am
Its effective Sir ! but is there any way to write the SQL for concatenate multiple records and show in on column?
October 7, 2014 at 4:45 am
Here are all the options:
Concatenating Row Values in Transact-SQL[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply