Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Select merge rows

Author  Topic 

johnc81
Starting Member

3 Posts

Posted - 2014-07-25 : 04:20:03
Hello,

I am trying to build a SQL select query, but am struggling a little bit.

I have a table with multiple inventory transactions, each with an ID and weight.

For example:

proc_packxacts
--------------
ID SysProg Weight
210359 271 986
210359 238 985
210359 61 984
210359 5 983
954679 271 1000
354852 271 568
354852 238 523
354852 59 521
354852 5 520

What I want to do is write a SQL query that returns records where there is a transaction for SysProg = 238, but return the weights for some of the other transactions in columns on the same row.

Basically, from the above data set, what I would like to return is:

ID Weight (271) Weight (238) Weight (61 or 59)
210359 986 985 984
354852 568 523 521

I have no idea how to start this? Any help would be very much appreciated.

Kind Regards,

John

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-25 : 05:36:19
[code]DECLARE @Sample TABLE
(
ID INT NOT NULL,
SysProg INT NOT NULL,
[Weight] INT NOT NULL
);

INSERT @Sample
(
ID,
SysProg,
[Weight]
)
VALUES (210359, 271, 986),
(210359, 238, 985),
(210359, 61, 984),
(210359, 5, 983),
(954679, 271, 1000),
(354852, 271, 568),
(354852, 238, 523),
(354852, 59, 521),
(354852, 5, 520);

DECLARE @SysProg INT = 238;

-- SwePeso
WITH cteSource(ID, [Weight], rn, Found)
AS (
SELECT ID,
[Weight],
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SysProg DESC) AS rn,
SUM(CASE WHEN SysProg = @SysProg THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS Found
FROM @Sample
)
SELECT ID,
MAX(CASE WHEN rn = 1 THEN [Weight] ELSE NULL END) AS Col1,
MAX(CASE WHEN rn = 2 THEN [Weight] ELSE NULL END) AS Col2,
MAX(CASE WHEN rn = 3 THEN [Weight] ELSE NULL END) AS Col3
FROM cteSource
WHERE Found = 1
GROUP BY ID;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

johnc81
Starting Member

3 Posts

Posted - 2014-07-25 : 06:18:32
Hello SwePeso,

Thanks for the query above. When I run the above, it returns exactly what I want :)

How would I get that to run over my dataset? You have hardcoded the values, but I need to pull them from my table. Also, how do you only ignore the SysProg = 5?

Kind Regards,

John
Go to Top of Page
   

- Advertisement -