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
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
it works but i need the results in different columns eg app1 its own column,app2 its own column & so on
I think the phrase you were looking for is "THANK YOU"...
Kristen
October 10, 2017, 11:38am
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.
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;