How to loop subquery to return multiple values if they exist

Transact-SQL
1

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

2

XML methods aren't the best place to start learning SQL but it's better than cursors & while loops I suppose...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	shopName CHAR(5),
	Device VARCHAR(10)
	);
INSERT #TestData (shopName, Device) VALUES
	('Shop1', 'app1'),
	('Shop1', 'app2'),
	('Shop1', 'sam1'),
	('Shop2', 'app67'),
	('Shop3', 'app82'),
	('Shop3', 'sam028');

--===============================================

SELECT 
	All_Jammed_Up = CONCAT(td1.shopName, MAX(d.Devise_Csv))
FROM 
	#TestData td1
	CROSS APPLY ((
		SELECT 
			CONCAT('|', td2.Device)
		FROM 
			#TestData td2
		WHERE 
			td1.shopName = td2.shopName
		FOR XML PATH('')
		)) d (Devise_Csv)
	
GROUP BY 
	td1.shopName;

The results...

All_Jammed_Up
----------------------
Shop1|app1|app2|sam1
Shop2|app67
Shop3|app82|sam028
3

it works but i need the results in different columns eg app1 its own column,app2 its own column & so on

4

I think the phrase you were looking for is "THANK YOU"...

5

nothing like the same as

Also, in general terms, a really bad idea to store multiple attributes as multiple columns, rather than multiple rows in a child table. Only exception, to my mind, would be where some import data existed that was:

PKey, AttributeID, AttributeValue

and the various Attribute IDs were unique per PKey and needing flattening (e.g. UNPIVOT) into multiple columns.

6

What you are looking for is either a PIVOT or CROSS TAB. There are many examples available that you can find through google...

This is a cross-tab solution

Declare @testData Table (shopName char(5), Device varchar(10));

 Insert @TestData (shopName, Device) 
 Values ('Shop1', 'app1')
      , ('Shop1', 'app2')
      ,	('Shop1', 'sam1')
      , ('Shop2', 'app67')
      , ('Shop3', 'app82')
      , ('Shop3', 'sam028');

 --==== Rank the Devices
   With rankedDevices
     As (
 Select *
      , deviceRank = row_number() over(Partition By shopName Order By Device)
   From @testData
        )
 --==== Cross-Tab the ranked devices
 Select shopName
      , device1 = max(Case When deviceRank = 1 Then Device End)
      , device2 = max(Case When deviceRank = 2 Then Device End)
      , device3 = max(Case When deviceRank = 3 Then Device End)
   From rankedDevices
  Group By
        shopName;