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(varchar,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
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
15 3915
Here, read this and tweak it a bit for you requirement.
Happy Coding!!!
~~ CK
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,'')
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.
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.
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.
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.userid, a.status,ISNULL(Convert(varchar,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,'')
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
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.
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.
Are you saying you need all rows to be in a single column? How big is your table and not grouped together?
~~ CK
@ck9663
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.
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
[quote=ck9663;3696026]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
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
This should be equivalent. - SELECT 'xxx', STUFF((
-
SELECT ' ** ' + [name] + '-' + code + '-' + xtime
-
FROM @tbl
-
ORDER BY [name], code, xtime, xxx
-
FOR XML PATH(''), TYPE, ROOT
-
).value('root[1]','nvarchar(max)'),1,4,'')
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Garry |
last post by:
Hi, I am new to python, hope someone can help me here:
I have a MS Access exported .txt file which is tab delimited in total
20 columns, now I need to add another column of zero at the 4th column...
|
by: Deepa |
last post by:
Hi
I have a DataSet file (xml) which I need to convert it into a tab delimited
file. I need to write a C# console application for doing the same. Can anyone
help me out with the code to do it? I'd...
|
by: DC Gringo |
last post by:
I am outputting a datatable to a tab-delimited file. This all works well
and good with 2 issues:
1) There is some data in my table that looks like this:
<a_tag>theData</a_tag>. I would like to...
|
by: Marek Mänd |
last post by:
Please explain to a experienced fool like me, how to hide table rows
correctly at this case.
http://marekmand.kuubik.ee/iebug_canthide_table_rows_properly.htm
Click on the header "label" and see...
|
by: Dixie |
last post by:
How do I, in VBA from an access form module, add 5 rows to the top of a
spreadsheet called MySpreadsheet.xls. The worksheet is called MyWorksheet
and there is already data in the first 5 rows. I...
|
by: Yama |
last post by:
Hi,
I am looking to create a report comma delimited on a click of a button.
Explanantion:
1. Get from the database: "SELECT * FROM Customers WHERE Region = 'CA'"
2. Use either DataReader or...
|
by: KBuser |
last post by:
I'm working on a program which reads through a directory of excel files
and parses them into a delimited text file. However, in each date field
12:00:00 AM is added at the end of the date (format:...
|
by: Vmusic |
last post by:
Hi,
I am trying to write out an array of string variables to Notepad. I
can't get SendKeys to accept the string variable only literal quoted
strings.
I DO NOT want the hassle of writing to a...
|
by: shantanu |
last post by:
Hi all
i am trying to save a excel file as a tab delimited text file. can i
do it some how?
i using the code
_sheet1.SaveAs(@"C:\PRP
\PRPTemp.txt",Excel.XlFileFormat.xlTextWindows...
|
by: indona |
last post by:
i am trying to read a 2 way delimited txt file into sqlserver in VS2008 using C# and SQL server2005. the individual entries of the file are delimited by "~" and the data within the entries are...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |