none
SQL query to Pivot conversion in T-Sql RRS feed

  • Question

  • Hello Experts,

    I have a requirement to convert a SQL query to Pivot in T-SQL

    Please find the attached Screen Shot and it has the requirement on it. I want the output from cell A12 -> R12 ?

    Thank in advance for your help.


    Dasari

    Wednesday, July 8, 2020 5:39 AM

Answers

  • Here is the start of the code for you and you can use it for your other types to continue. Since you need multiple values to pivot, it's easier to do this way:

    SELECT T.ProdNo, T.ResourceNumber, T.[Name], T2.ResourceNumber as [Resource], 
    T.Qty as [Qty Estimated],  T.[Metres] as [Linear Metres Estimated],
    RS.[Consumption] as [RAWSTEEL (Consumption)], RS.[Value] as [RAWSTEEL (Value)]
    FROM Test T
    OUTER APPLY (select * from Test T2 where T2.ProdNo = T.ProdNo and T2.[Type] = 'M') T2
    OUTER APPLY (select * from Test T2 where T2.ProdNo = T.ProdNo and T2.[Type] = 'RS') RS
    where T.[Type] = 'MF'

    Alternative approach that may work faster will be

    SELECT T.ProdNo, max(case when T.Type  = 'MF' then Resource Number end) as ResourceNumber,

    max(case when T.Type = 'MF' then [Name] end) as Name, max(case when T.[Type] = 'M' then ResourceNumber end) as Resource, etc.

    from Test T GROUP BY T.ProdNo

    --------------------

    Use correct types to get values and column names you need - using MAX approach combined with correct types will allow you to create the desired result - I would personally go with this solution instead of the one I showed as first.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Jeevan Dasari Thursday, July 9, 2020 4:06 AM
    Wednesday, July 8, 2020 1:37 PM
    Moderator

All replies

  • Can you post CREATE TABLE + INSERT INTO + Desired result? See  example

    CREATE TABLE Foo (
      foo_type CHAR(1) PRIMARY KEY,
      foo_value INT);

    INSERT INTO Foo VALUES('A', 1);
    INSERT INTO Foo VALUES('B', 2);
    INSERT INTO Foo VALUES('C', 3);
    INSERT INTO Foo VALUES('D', 4);

    -- pivot using CASE
    SELECT MAX(CASE WHEN foo_type = 'A' THEN foo_value END) AS A,
            MAX(CASE WHEN foo_type = 'B' THEN foo_value END) AS B,
            MAX(CASE WHEN foo_type = 'C' THEN foo_value END) AS C,
            MAX(CASE WHEN foo_type = 'D' THEN foo_value END) AS D
    FROM Foo;

    -- pivot using PIVOT operator (SQL Server 2005/2008)
    SELECT A, B, C, D
    FROM Foo
    PIVOT
    (MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 8, 2020 6:14 AM
    Answerer
  • You screenshot is not very useful, please post table design & some sample data as SQL statement, see POSTING TIPS - Code, Images, Hyperlinks, Details

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 8, 2020 6:41 AM
  • Hi Uri thank you for your reply.

    Please find the SQL code below as requested

    CREATE TABLE Test (
    ProdNo INT
    , ResourceNumber VARCHAR(25)
    ,Type VARCHAR(05)
    ,Name VARCHAR(25)
    ,Prodcution INT
    ,Consumption Decimal(38,2)
    ,Qty INT, Metres Decimal(38,0),Value Float
    )

    INSERT INTO Test VALUES (11111, '29','M','T1', 0,0.42,0,600,63.75)
    INSERT INTO Test VALUES (11111, '78L1','L','L1',0,0.42,200,600,31.61)
    INSERT INTO Test VALUES (11111, 'C1','C1','T1',0,95.36,50,600,10.4896)
    INSERT INTO Test VALUES (11111, 'C2','C2','T2',0,95.36,50,0,6.6752)
    INSERT INTO Test VALUES (11111, 'C3','C3','T3',0,2229.482812,0,0,17.835862496)
    INSERT INTO Test VALUES (11111, '1ZZZ0555','RS','SSSSSSSAAAAA',0,1653.919,0,0,2229.482812)
    INSERT INTO Test VALUES (11111, '111000','MF','Industry',0,4074.96,300,1800,2359.8434)

    --SELECT * FROM Test

    I want the below result using PIVOT.

    Thank you


    Dasari


    Wednesday, July 8, 2020 7:04 AM
  • Can Any one help with the above ?


    Thank you


    Dasari


    Wednesday, July 8, 2020 9:23 AM
  • Can Any one help wit the above ?

    Did you see the replies? Several persons tried to help you.

    If that was on the mark, you need help people to help you. For this kind of problems it is always a good idea to post CREATE TABLE statements for your table(s) together with INSERT statements with sample data, enough to illustrate all angles of the problem. And obviously, we need to know the desired result given the sample data. That makes it possible to copy and paste into a query window to develop a tested solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 8, 2020 11:06 AM
  • Here is the start of the code for you and you can use it for your other types to continue. Since you need multiple values to pivot, it's easier to do this way:

    SELECT T.ProdNo, T.ResourceNumber, T.[Name], T2.ResourceNumber as [Resource], 
    T.Qty as [Qty Estimated],  T.[Metres] as [Linear Metres Estimated],
    RS.[Consumption] as [RAWSTEEL (Consumption)], RS.[Value] as [RAWSTEEL (Value)]
    FROM Test T
    OUTER APPLY (select * from Test T2 where T2.ProdNo = T.ProdNo and T2.[Type] = 'M') T2
    OUTER APPLY (select * from Test T2 where T2.ProdNo = T.ProdNo and T2.[Type] = 'RS') RS
    where T.[Type] = 'MF'

    Alternative approach that may work faster will be

    SELECT T.ProdNo, max(case when T.Type  = 'MF' then Resource Number end) as ResourceNumber,

    max(case when T.Type = 'MF' then [Name] end) as Name, max(case when T.[Type] = 'M' then ResourceNumber end) as Resource, etc.

    from Test T GROUP BY T.ProdNo

    --------------------

    Use correct types to get values and column names you need - using MAX approach combined with correct types will allow you to create the desired result - I would personally go with this solution instead of the one I showed as first.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Jeevan Dasari Thursday, July 9, 2020 4:06 AM
    Wednesday, July 8, 2020 1:37 PM
    Moderator
  • Hi Erland,

    Thank you for the above message. I did posted Create table and insert statements but looks like you haven't noticed it.

    Anyways mate I have used Naomi and resolved the issue.

    Thank you.


    Dasari

    Thursday, July 9, 2020 3:17 AM