-1

Apologies in advance, I have minimal experience in the SQL realm. One of our systems delivers data in a format that places individual lines items from a single record into separate rows. An example:

Input

Key   Desc   LineItem
55    AAA    1
55    BBB    2
66    CCC    1

I am looking to query this data in a way that would return the values for each Key as one line based on the line item number:

Output

Key   Desc1  Desc2
55    AAA    BBB
66    CCC    Null

Output.Desc1 = Input.Desc where Input.LineItem = 1
Output.Desc2 = Input.Desc where Input.LineItem = 2

Understanding how to return the desired output from my example should allow me to apply it to the larger data set. Thank you in advance for any assistance!

  • 1
    Would you only ever have LineItem values in (1, 2)? This looks like a straightforward case for using PIVOT. – mathewb Oct 16 '18 at 20:02
  • The Line Item Details could be as many as 30 I believe. – Brian Pierce Oct 16 '18 at 21:18
1

As mentioned by @mathewb that PIVOT is an option for what you are looking for. I did work out an example for you. Using similar data that you put in your question.

Ref: FROM - Using PIVOT and UNPIVOT

DROP DATABASE IF EXISTS testPivot;
CREATE DATABASE testPivot;
GO

USE [testPivot]
GO

DROP TABLE IF EXISTS dbo.input;
CREATE TABLE dbo.input 
    ([keyItem] int , 
     [description] varchar(255), 
     [lineItem] smallint);
GO

SET NOCOUNT ON;
GO

DECLARE @a int;
SELECT @a = 55;
WHILE (@a < 65)
BEGIN
    DECLARE @b int;
    SELECT @b = 1;
    WHILE (@b < 4)
    BEGIN
        INSERT INTO input VALUES (@a, CONVERT(varchar(255), NEWID()), @b );
        SELECT @b = @b + 1;
    END
    SELECT @a = @a + 1;
END;
GO

SELECT * FROM dbo.input

SELECT [keyItem],
 [1] as [desc1], [2] as [desc2], [3] as [desc3]
FROM
 (SELECT 
    [keyItem],
    [lineitem],
    [description]
  FROM dbo.input) 
    AS sourcetable
PIVOT
    (MIN([description]) FOR lineitem IN ([1],[2],[3]))
  AS pivottable

Here is my result for input and output. enter image description here

  • Thank you @SqlWorldWide, your solution gave me a deeper understanding of the pivot function. – Brian Pierce Oct 17 '18 at 16:31
0

Thank you @mathewb for the PIVOT suggestion. Very specific to my data example, I was able to query the data with the following. @SqlWorldWide provided a much more elaborate solution to my question.

SELECT Key
 ,[1] AS Desc1
 ,[2] AS Desc2
FROM
 (SELECT ReqKey, Desc, LineItem
 FROM Input) p
PIVOT
(
 MAX (Desc)
 FOR LineItem IN ([1], [2])
) AS a

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.