Hey all here is my issue.
My query output looks like this (stripped down to a few rows):
|number |line |partNum |phoneNum |qty_SN |qty_Property |qty_Name |qty_Time
------------------------------------------------------------------------------
|87 |1 |55G5 |555-789-7512 |00123 |Local |Owner |05:22
|87 | | | |14988 |Local |Seller |10:44
|87 | | | |521 |Remote |Owner |01:05
|87 | | | |50697 |Local |Seller |11:41
|87 | | | |2359 |Remote |Seller |04:45
And what I am looking to want to do is this:
|number |line |partNum |phoneNum |qty_SN |qty_Property |qty_Name |qty_Time
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|87 |1 |55G5 |555-789-7512 |00123,14988,521,50697,2359 |Local,Local,Remote,Local,Remote |Owner,Seller,Owner,Seller,Seller |05:22,10:44,01:05,11:41,04:45
Notice that all the qty_ columns are combined into the first full data row instead of being on their own row like in the first table.
The query for the first table looks like this (again, stripped down):
;WITH TheDATA AS (
SELECT
BL.number AS 'number',
BL.line AS 'line',
BL.partNumber AS 'partNum',
BL.phoneNumber AS 'phoneNum',
L.qtySN AS 'qty_SN',
I.qtyProperty AS 'qty_Property',
I.qtyName AS 'qty_Name',
I.qtyTime AS 'qty_Time',
ROW_NUMBER() Over (
PARTITION BY
BL.number
ORDER BY
BL.number
) AS 'RowNo'
FROM
BList AS BL
INNER JOIN Location AS L
ON BL.ID = L.Route
INNER JOIN Inventory AS I
ON L.ID = I.prodID
)
SELECT
number AS 'number',
IIF(RowNo = 1, CONVERT(varchar, line), '') AS 'line',
IIF(RowNo = 1, partNumber, '') AS 'partNum',
IIF(RowNo = 1, phoneNumber, '') AS 'phoneNum',
qty_SN AS 'qty_SN',
qty_Property AS 'qty_Property',
qty_Name AS 'qty_Name',
qty_Time AS 'qty_Time'
FROM
TheData
And ive been messing around with it and this is what I came up with that combines the needed values but it shows 2 rows instead of one.
;WITH TheDATA AS (
SELECT
BL.number AS 'number',
BL.line AS 'line',
BL.partNumber AS 'partNum',
BL.phoneNumber AS 'phoneNum',
L.qtySN AS 'qty_SN',
I.qtyProperty AS 'qty_Property',
I.qtyName AS 'qty_Name',
I.qtyTime AS 'qty_Time',
ROW_NUMBER() Over (
PARTITION BY
BL.number
ORDER BY
BL.number
) AS 'RowNo'
FROM
BList AS BL
INNER JOIN Location AS L
ON BL.ID = L.Route
INNER JOIN Inventory AS I
ON L.ID = I.prodID
)
SELECT DISTINCT
number AS 'number',
line AS 'line',
partNumber AS 'partNum',
phoneNumber AS 'phoneNum',
combinedSN = (
Stuff(
(
SELECT + ', ' +
CAST(qty_SerialNum AS Varchar(100))
FROM
TheDATA
FOR
XML PATH ('')
), 1, 1, ''
)
),
combinedProperty = (
Stuff(
(
SELECT + ', ' +
CAST(qty_Property AS Varchar(100))
FROM
TheDATA
FOR
XML PATH ('')
), 1, 1, ''
)
),
combinedName = (
Stuff(
(
SELECT + ', ' +
CAST(qty_Name AS Varchar(100))
FROM
TheDATA
FOR
XML PATH ('')
), 1, 1, ''
)
),
combinedTime = (
Stuff(
(
SELECT + ', ' +
CAST(qty_Time AS Varchar(100))
FROM
TheDATA
FOR
XML PATH ('')
), 1, 1, ''
)
)
FROM
TheData
Please see the demo
rextester[dot]com/RZOLB74322