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 Weight210359 271 986210359 238 985210359 61 984210359 5 983954679 271 1000354852 271 568354852 238 523354852 59 521354852 5 520What 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 984354852 568 523 521I 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;-- SwePesoWITH 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 Col3FROM cteSourceWHERE Found = 1GROUP BY ID;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
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 |
|
|
|
|
|