Two Rows in one column SQL Server

Question

I have the following query:

SELECT tbl_SampledParts.Data 
FROM tbl_SampledParts 
INNER JOIN tbl_Inspection ON tbl_SampledParts.InspectionId = tbl_Inspection.InspectionId
WHERE (tbl_Inspection.InspectionDate BETWEEN '2016-10-26' AND '2016-11-03')

That's what I get but I want to show the query results in only one column I tried using COALESCE and XML queries from answers in this page but I do not know how to add my inner join and where conditions. This query show the results like I want:

DECLARE @test NVARCHAR(max)  

SELECT @test = COALESCE(@test + ',', '') + Data FROM tbl_SampledParts 

SELECT @test

But like I said I need my inner join and where conditions mandatory, how can I do that?

UPDATE: Dates can change so that's the reason of why I need to use a where condition exactly like this WHERE (tbl_Inspection.InspectionDate between '2016-10-26' and '2016-11-03').

So u basically want to unpivot the data and have it in a single column. Is that correct? — Teja, Nov 03 '16 at 20:37
Please refer to below post: http://stackoverflow.com/questions/9395444/concatenating-rows-from-a-stored-procedure-onto-another-table-in-tsql — shanyour, Nov 03 '16 at 20:37
@Teja Yes, something like that, right now I'm looking for some information about pivot/unpivot but I have the same problem, I don't know how to combine that with my inner join a where condition — Miguel Flores, Nov 03 '16 at 20:38
@shanyour I understand that, but I need to use my where condition, because depends the selected dates I'm going to get my result — Miguel Flores, Nov 03 '16 at 20:41
Can you provide the expected output should look like. You say "I want to show the query results in only one column", yet that's exactly what you have here. — S3S, Nov 03 '16 at 20:44
@scsimon Look, my normal query results are the same from the above image, what I want is to combine that two results in one column, doing the query with `COALESCE` I get the results in the expected way but if you can see in that query I do not have any where condition, so in other words I want to combine the `COALESCE` query with my inner join and where conditions. — Miguel Flores, Nov 03 '16 at 20:48
You only have one COLUMN.... You have two ROWS. They are different. Do you mean you want to combine the rows? — S3S, Nov 03 '16 at 20:50
@scsimon Yes, I want to combine the rows with my inner join and where conditions — Miguel Flores, Nov 03 '16 at 20:52
Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — S3S, Nov 03 '16 at 20:54
@scsimon Maybe my question is not much clear, I see that question that you said, but in that question and all the others they are doing like a kind of `select * from` in my case I want to do `select data where date between`, that's what I want combine the answers with my conditions but I don't know exactly how to do it — Miguel Flores, Nov 03 '16 at 20:57
I do not see any correlation between the first query and the output image, something is missing. why do you get 2 rows? is it always 2 rows? **How many rows are in this query** `select count(*) tbl_Inspection WHERE (tbl_Inspection.InspectionDate between '2016-10-26' and '2016-11-03')` — Paul Maxwell, Nov 03 '16 at 20:59

ralf.w. · Accepted Answer · 2016-11-03T21:03:10.393

1

You can combine both of your queries like this:

DECLARE @test NVARCHAR(max);

SELECT @test = COALESCE(@test + ',', '') + Data FROM (
SELECT tbl_SampledParts.Data FROM tbl_SampledParts 
INNER JOIN tbl_Inspection ON tbl_SampledParts.InspectionId = tbl_Inspection.InspectionId
WHERE (tbl_Inspection.InspectionDate between '2016-10-26' and '2016-11-03')
) as t;

SELECT @test;

that's it

edited Nov 03 '16 at 21:03

answered Nov 03 '16 at 20:55

ralf.w.

1,676
1
24
34

Maybe there is a syntax error because I get `Incorrect syntax near the keyword 'Select'.` – Miguel Flores Nov 03 '16 at 21:00
1

it needs an alias after the closing parenthesis row 6 – Paul Maxwell Nov 03 '16 at 21:01

score 0 · Answer 2 · answered Nov 03 '16 at 20:54

create table Testdata(Data varchar(max))
insert Testdata select '3.6,1,5.6,7.5,9,2.2,4.6,2.3,6.4,6.5,5,3.7,6,5.4,3,1.5'
insert Testdata select '50,3.72,50,3.72,50,3.72,50,3.72,65.3,72,50,3.72,45,3.72,..'



with tmp(DataItem, Data) as (
select LEFT(Data, CHARINDEX(',',Data+',')-1),
       STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from Testdata
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
       STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select DataItem
from tmp;

Two Rows in one column SQL Server

2 Answers2