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
– Teja, Commented Nov 3, 2016 at 20:37
Please refer to below post: stackoverflow.com/questions/9395444/… — shanyour
– shanyour, Commented Nov 3, 2016 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
– Miguel Flores, Commented Nov 3, 2016 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
– Miguel Flores, Commented Nov 3, 2016 at 20:41
You only have one COLUMN.... You have two ROWS. They are different. Do you mean you want to combine the rows? — S3S
– S3S, Commented Nov 3, 2016 at 20:50

ralf.w. · Accepted Answer · 2016-11-03 21:03:10Z

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 3, 2016 at 21:03

answered Nov 3, 2016 at 20:55

ralf.w.

1,6941 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Miguel Flores Over a year ago

Maybe there is a syntax error because I get Incorrect syntax near the keyword 'Select'.

Paul Maxwell Over a year ago

it needs an alias after the closing parenthesis row 6

Teja · Accepted Answer · 2016-11-03 20:54:21Z

0

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;

answered Nov 3, 2016 at 20:54

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Collectives™ on Stack Overflow

Two Rows in one column SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related