Help Need in join

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  August 10, 2015 at 8:53 pm

  #306591

  Hi,

  Below are the sample data and expected output

  declare @Users table(UserID int identity(1,1) primary key,Name varchar(50),Active bit);

  declare @CreditCards table(IdCredicard int identity(1,1) primary key, UserID int,CardNo bigint,Active bit,Date_Created datetime);

  insert into @Users(Name,Active)

  select 'Name1',1 union all

  select 'Name2',1 union all

  select 'Name3',1 union all

  select 'Name4',1 union all

  select 'Name4',1 ;

  insert into @CreditCards(UserID,CardNo,Active,Date_Created)

  select 1,1000,1,GETDATE() union all

  select 1,1001,1,GETDATE() union all

  select 1,1002,1,GETDATE() union all

  select 2,1003,1,GETDATE() union all

  select 2,1004,1,GETDATE() union all

  select 3,1005,1,GETDATE() union all

  Expected output:

  select 'P'+'|' +CAST(1 as varchar)+'|' +'Name1'+'|'+cast(1 as varchar) union all

  select 'C'+'|' + cast(1 as varchar) + '|' + cast(1000 as varchar) + '|' + cast(1 as varchar) + '|' + convert(varchar,GETDATE(),101) union all

  select 'C'+'|'+ cast(1 as varchar) + '|' + cast(1001 as varchar) + '|' + cast(1 as varchar) + '|' + convert(varchar,GETDATE(),101) union all

  select 'C' + '|' + cast(1 as varchar) + cast(1002 as varchar) + '|' + cast(1 as varchar) + '|' + convert(varchar,GETDATE(),101) union all

  select 'P'+'|'+CAST(2 as varchar)+'|'+'Name2'+'|'+cast(1 as varchar) union all

  select 'C' + '|' +CAST(2 as varchar) + '|' + cast(1003 as varchar) + '|' + cast(1 as varchar) + '|' + convert(varchar,GETDATE(),101) union all

  select 'C' + '|'+CAST(2 as varchar) + '|' + cast(1004 as varchar) + '|' + cast(1 as varchar) + '|' + convert(varchar,GETDATE(),101) union all

  select 'P'+'|'++CAST(3 as varchar)+'|'+'Name3'+'|'+cast(1 as varchar) union all

  select 'C' + '|' +CAST(3 as varchar)+'|' + cast(1005 as varchar) + '|' + cast(1 as varchar) + '|' + convert(varchar,GETDATE(),101)

  Note : UserID is Primarykey in users table and foreign key in creditcard table. Please help me on writing the best way to achieve this logic. Any sample query will be much appreciated.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  August 10, 2015 at 9:33 pm

  #1818788

  Are you sure that's the output you're expecting? Don't you mean you just want a normal output that you get from a comma-separated list of column names? Shouldn't the sample output be something like:

  SELECT 'textstring' AS colName1, 125 as colName2

  UNION ALL

  SELECT 'textstring2', 132

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  August 10, 2015 at 10:44 pm

  #1818794

  Quick suggestion

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  declare @Users table(UserID int identity(1,1) primary key,Name varchar(50),Active bit);

  declare @CreditCards table(IdCredicard int identity(1,1) primary key, UserID int,CardNo bigint,Active bit,Date_Created datetime);

  insert into @Users(Name,Active)

  select 'Name1',1 union all

  select 'Name2',1 union all

  select 'Name3',1 union all

  select 'Name4',1 union all

  select 'Name5',1 ;

  insert into @CreditCards(UserID,CardNo,Active,Date_Created)

  select 1,1000,1,GETDATE() union all

  select 1,1001,1,GETDATE() union all

  select 1,1002,1,GETDATE() union all

  select 2,1003,1,GETDATE() union all

  select 2,1004,1,GETDATE() union all

  select 3,1005,1,GETDATE() ;

  SELECT

  IT.UserID

  ,STUFF((

  SELECT

  CHAR(124) + CONVERT(VARCHAR(24),SI.Date_Created,101)

  FROM @CreditCards SI

  WHERE SI.UserID = IT.UserID

  FOR XML PATH('')),1,1,'') AS [ConcatColumn]

  FROM @Users IT;

  Results

  UserID ConcatColumn

  ----------- ----------------------------------

  1 08/11/2015|08/11/2015|08/11/2015

  2 08/11/2015|08/11/2015

  3 08/11/2015

  4 NULL

  5 NULL

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  August 11, 2015 at 5:53 am

  #1818869

  Hi piet,

  Thanks and union all with few additional condition did the trick

  Hi Eirik,

  Thanks and Could you please help me on understanding why do need to use xmlpath for this logic? union all did the trick. Also, just for my curiosity, the sample query didn't product the expected result. Any clue please

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 11, 2015 at 7:15 am

  #1818931

  This article explains the FOR XML PATH approach.

  http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  August 11, 2015 at 8:32 pm

  #1819204

  Hi Luis,

  Thanks for the link and I created CTE on top of Eirik query and get the output as i needed.

  thanks Eirik for the base logic.