how to loop subquery to return multiple values if they exist

Asked Oct 09 '17 at 20:10

Active Oct 09 '17 at 20:23

Viewed 40 times

i am still learning SQL. I have the following table DeviceType

|shopName|Device
---------|-----------
|Shop1   |app1
|Shop1   |app2
|Shop1   |sam1
|Shop2   |app67
|Shop3   |app82
|Shop3   |sam028

i am trying to get the following result

Shop1|app1|app2|sam1
Shop2|app67
Shop3|app82|sam028

my code is as follows:

select Shopname,(SELECT device FROM DeviceType where left(SNTxReference,3)='app'     )
from DeviceType

but i get Subquery returned more than 1 value

edited Oct 09 '17 at 20:23

jarlh

asked Oct 09 '17 at 20:10

Tinashe

1

What [tag:rdbms] are you using? – Mureinik Oct 09 '17 at 20:14
Is there a maximum of devices per shop? – Radim Bača Oct 09 '17 at 20:17
@Mureinik i am using MS SQL Server 2012 – Tinashe Oct 09 '17 at 20:22
SQL is a language, 2012 is a year. MS SQL Server 2012 is a dbms product. – jarlh Oct 09 '17 at 20:23
@ Radim no there is no max, if it fails to find it should stop – Tinashe Oct 09 '17 at 20:24
What you are doing is challenging with relational data because it is no longer relational. You will have to use a dynamic pivot for this. – Sean Lange Oct 09 '17 at 20:29
@SeanLange Or `STUFF` if he wants them pipe delimited and in one column. – justiceorjustus Oct 09 '17 at 20:29
What problem are you trying to solve by structuring the result set that way, @Tinashe? I'm wondering if it's really necessary or if there might be a better approach. – Joe Farrell Oct 09 '17 at 20:32
I think you are more looking at something like this: https://stackoverflow.com/questions/7958816/how-to-get-multiple-rows-into-one-line-as-a-string – Alan Oct 09 '17 at 21:48

0 Answers0