Mutilple rows should write into one row with TAB delimited

[Kim Nat]

I am using SQL SERVER 2008 R2 and I have situation like simple select gives multiple rows from two tables with inner join. I want to get simply these mutilple rows into one single row with TAB delimited.

Here is my simple SQL

Select 'xxx',a.userid, a.status,ISNULL (Convert(varcha r,a.time,108),' ' ) AS TIME,a.uid
from TABLE AS a WITH (NOLOCK)
Inner join TABLE AS b WITH (NOLOCK) on
a.status = b.statuscode

I am getting the data by using above SQL as

xxx john CMD 10:50:06 plk
xxx smith CMDT 10:50:29 smk
xxx chris CMDT 10:50:29 ll
xxx smith CMD 10:50:38 plk

where as I want to get this in all in ONE line like below

xxx john CMD 10:50:06 plk smith CMDT 10:50:29 smk chris CMDT 10:50:29 lll smith CMD 10:50:38 plk

[ck9663]

Here, read this and tweak it a bit for you requirement.

Happy Coding!!!


~~ CK

[Rabbit]

You could use the XML capabilities of SQL Server to aggregate strings.

STUFF((
   SELECT ',' + someField FROM someTable
   FOR XML PATH(''), TYPE, ROOT
).value('root[1]','nvarchar(max)'),1,1,'')

[Kim Nat]

Thanks CK. I can't find how to tweak your code for my requirement, or may be I am unable to catch the logic that you had written in this code. I am not worried about TAB delimited, if I know the code for just make multiple rows of data into one single row is enough. TAB values I can obtain easily but I want the logic for getting all in one row when retrieving from SQL SERVER these multiple records.

[Kim Nat]

Hi Rabbit, I am not supposed to use XML in my requirement where as only SQL needed to generate these multiple records into one single row.

[Rabbit]

Is this homework? Because that's an odd restriction. Whether or not you use the XML functionality, the end result will be exactly the same.

P.S. The result is not XML if that's what you're thinking.

[Kim Nat]

Hi Rabbit
Thanks for your reply

It is not home work, yes the output I thought it is in XML. I did try my above SQL to use your logic and some how I got into issues when running is SSMS the code I am trying however I am still gettting those XML flags in the result.

Here is my code after I adjusted based on your suggestion but still getting the XML flags

Select ',','xxx',a.use rid, a.status,ISNULL (Convert(varcha r,a.time,108),' ' ) AS TIME,a.uid
from TABLE AS a WITH (NOLOCK)
Inner join TABLE AS b WITH (NOLOCK) on
a.status = b.statuscode
FOR XML PATH(''), TYPE, ROOT


I am not sure how to use your last line of code that VALUE into my SQL I am getting Syntax errors when I try to use this :- ).value('root[1]','nvarchar(max )'),1,1,'')

[ck9663]

Here... I tweaked my code.

I don't know if you want the tab in between columns or in between each added rows. Tab is an unprintable character so I used dash (-)in between columns and I used two asterisk (**) in between each added row. Modify that part as you see fit.

Sample data population...

declare @tbl table (id varchar(3), name varchar(10), code varchar(10), xtime varchar(15), xyz varchar(6))

insert into @tbl
select 'xxx' id ,'1john' name,'CMD' code,'10:50:06' xtime,'plk' xyz
union all
select 'xxx','1smith','CMDT','10:50:29','smk'
union all
select 'xxx','1chris','CMDT','10:50:29','ll'
union all
select 'xxx','1smith','CMD','10:50:38','plk'
union all
select 'abc','2smith','CMDT','10:50:29','smk'
union all
select 'abc','2chris','CMDT','10:50:29','ll'
union all
select 'abc','2smith','CMD','10:50:38','plk'
union all
select 'xyz','3smith','CMDT','10:50:29','smk'
union all
select 'xyz','3smith','CMD','10:50:38','plk'

select * from @tbl

Sample Data...

id   name       code       xtime           xyz
---- ---------- ---------- --------------- ------
xxx  1john      CMD        10:50:06        plk
xxx  1smith     CMDT       10:50:29        smk
xxx  1chris     CMDT       10:50:29        ll
xxx  1smith     CMD        10:50:38        plk
abc  2smith     CMDT       10:50:29        smk
abc  2chris     CMDT       10:50:29        ll
abc  2smith     CMD        10:50:38        plk
xyz  3smith     CMDT       10:50:29        smk
xyz  3smith     CMD        10:50:38        plk

Desired Result...

id   all_data
---- ------------------------------------------------------------------------------------------------------------------------
abc  abc-2chris-CMDT-10:50:29-ll ** abc-2smith-CMD-10:50:38-plk ** abc-2smith-CMDT-10:50:29-smk
xxx  xxx-1chris-CMDT-10:50:29-ll ** xxx-1john-CMD-10:50:06-plk ** xxx-1smith-CMD-10:50:38-plk ** xxx-1smith-CMDT-10:50:29-smk
xyz  xyz-3smith-CMD-10:50:38-plk ** xyz-3smith-CMDT-10:50:29-smk

Actual Code...

;with Numbered
as 
   (
      select       
         rownum = row_number() over(partition by id order by id, name, code, xtime, xyz),
         id, name, code, xtime, xyz
      from @tbl
   )
,FirstRecords
as 
   (
      select rownum, id, name, code, xtime, xyz from Numbered where rownum = 1
   )
,ReturnAllRecords
as
   (
      select rownum, id, cast(id + '-' + name + '-' + code + '-' + xtime + '-' + xyz  as varchar(500)) as all_data from FirstRecords  
      union all      
      select Numbered.rownum, Numbered.id,  cast(all_data + ' ** ' + Numbered.id + '-' + Numbered.name + '-' + Numbered.code + '-' + Numbered.xtime + '-' + Numbered.xyz  as varchar(500))
      from Numbered
         inner join ReturnAllRecords 
            on ReturnAllRecords.id = Numbered.id and
               ReturnAllRecords.rownum + 1 = Numbered.Rownum
   )
select id, max(all_data) as all_data
from ReturnAllRecords
group by id

Happy Coding!!!


~~ CK

[Rabbit]

You're missing a lot of stuff in yours that is in my post. And my post is just the column definition, not the entire SQL statement.

[Kim Nat]

Hi CK

Thanks for your help on this and also the logic much detailing to me. If you see the SQL I printed the value 'XXX' is not from table it is just introduced and I do have mutiple rows the remaining data. When I use that SQL I get 'XXX' values also multiple times and also I never get different ID other than 'XXX'. So my desired result is always should be as :

xxx john CMD 10:50:06 plk smith CMDT 10:50:29 smk chris CMDT 10:50:29 lll smith CMD 10:50:38 plk

I think I need to eliminate that Group by ID from your code, am I correct ? or should I elimnate some part of code ;withnumbered code too.

[ck9663]

Are you saying you need all rows to be in a single column? How big is your table and not grouped together?


~~ CK

[Kim Nat]

Are you saying you need all rows to be in a single column? How big is your table and not grouped together?


~~ CK

Yes I need all rows to be in one single column and I extract these rows as a part of my application requirement. I am unsure how many rows would genrate from the two tables join - it depends on sometimes 2 or 3 rows and some times may be maximum of 10 rows. Finally I want my desired outout must be in SINGLE row instead of mutilple rows.

[ck9663]

If it's a single table, you don't need to join it to itself...

Here's the modified code

set nocount on
 
declare @tbl table (name varchar(10), code varchar(10), xtime varchar(15), xxx varchar(6))
 
insert into @tbl
select '1john' name,'CMD' code,'10:50:06' xtime,'plk' xxx
union all
select '1smith','CMDT','10:50:29','smk'
union all
select '1chris','CMDT','10:50:29','ll'
union all
select '1smith','CMD','10:50:38','plk'
union all
select '2smith','CMDT','10:50:29','smk'
union all
select '2chris','CMDT','10:50:29','ll'
union all
select '2smith','CMD','10:50:38','plk'
union all
select '3smith','CMDT','10:50:29','smk'
union all
select '3smith','CMD','10:50:38','plk'
 
select * from @tbl

Sample data...

name       code       xtime           xxx
---------- ---------- --------------- ------
1john      CMD        10:50:06        plk
1smith     CMDT       10:50:29        smk
1chris     CMDT       10:50:29        ll
1smith     CMD        10:50:38        plk
2smith     CMDT       10:50:29        smk
2chris     CMDT       10:50:29        ll
2smith     CMD        10:50:38        plk
3smith     CMDT       10:50:29        smk
3smith     CMD        10:50:38        plk

Code...

  
;with Numbered
as 
   (
      select       
         rownum = row_number() over(partition by 1 order by name, code, xtime, xxx),
         name, code, xtime, xxx
      from @tbl
   )
,FirstRecords
as 
   (
      select rownum, name, code, xtime, xxx from Numbered where rownum = 1
   )
,ReturnAllRecords
as
   (
      select rownum, cast(name + '-' + code + '-' + xtime + '-' + xxx  as varchar(500)) as all_data from FirstRecords  
      union all      
      select Numbered.rownum, cast(all_data + ' ** ' + Numbered.name + '-' + Numbered.code + '-' + Numbered.xtime + '-' + Numbered.xxx  as varchar(500))
      from Numbered
         inner join ReturnAllRecords 
            on ReturnAllRecords.rownum + 1 = Numbered.Rownum
   )
select 
'xxx' col1, 
max(all_data) as all_data
from ReturnAllRecords

result..

---- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxx  1chris-CMDT-10:50:29-ll ** 1john-CMD-10:50:06-plk ** 1smith-CMD-10:50:38-plk ** 1smith-CMDT-10:50:29-smk ** 2chris-CMDT-10:50:29-ll ** 2smith-CMD-10:50:38-plk ** 2smith-CMDT-10:50:29-smk ** 3smith-CMD-10:50:38-plk ** 3smith-CMDT-10:50:29-smk

Happy Coding!!!


~~ CK

[Kim Nat]

[QUOTE=ck9663;36 96026]If it's a single table, you don't need to join it to itself...

Hi CK many thanks

It is not from single table I must need to obtain this data from two different tables with inner join. Any suggestion.

Once again thanks

[ck9663]

Then you need the join. That will be your input data which is what my @tbl is.

I think you do it from here...

Happy Coding!!!


~~ CK