none
How to convert rows into columns

    Question

  • Command Direction Values
    Input Af1 10
    Output AF2 100
    Default Af3

    1000

    I need th columns like below

    Command AF1 AF2

    Input  10 100

    • Moved by Olaf HelperMVP Saturday, July 14, 2018 2:55 AM Moved from "Database Design" to a more related forum
    Wednesday, May 30, 2018 9:31 AM

All replies

  • sorry didnt understand your output

    how did you merge the rows with different values for command?

    assuming it was a typo , what you need is this

    SELECT Command,
    MAX(CASE WHEN DIrection = 'Af1' THEN Values END) AS AF1,
    MAX(CASE WHEN DIrection = 'Af2' THEN Values END) AS AF2,
    FROM Table
    GROUP BY Command

    if you want to make it dynamic

    see

    https://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

    Otherwise as per your current output what you need is this which makes no sense to me 

    SELECT MAX(CASE WHEN Seq = 1 THEN Command END) AS Command,
    MAX(CASE WHEN DIrection = 'Af1' THEN Values END) AS AF1,
    MAX(CASE WHEN DIrection = 'Af2' THEN Values END) AS AF2,
    FROM 
    (
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY Command) AS Seq
    FROM Table
    )t
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, May 30, 2018 9:46 AM
  • Can you give this query using pivot clause
    Wednesday, May 30, 2018 9:55 AM
  • Can you give this query using pivot clause

    like this

    SELECT *
    FROM Table
    PIVOT(MAX(Values) FOR Direction IN ([Af1],[Af2]))p


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, May 30, 2018 10:05 AM
  • I need the below table in pivot format

    the above query is not giving the required outcome

    sharing you the sample data

    Input

    Name

    Class Value
    CEF af1 1048000
    EDC af2

    1048000   

    OUTPUT

    Name af1 af2 af3
    CEF 1048000 1100 120000
    EDC 1048000 110000 124000

    Wednesday, May 30, 2018 1:25 PM
  • Hello Ychinnari,

    May be below query can help you. your desired result set is not clear to me. so i made a query with my understanding on your desired result set. 

    CREATE TABLE #TT(Name VARCHAR(100),Class VARCHAR(100),Value INT)
    INSERT INTO #TT(NAME,CLASS,VALUE)
    SELECT 'CEF','af1',1048000 UNION 
    SELECT 'EDC','af2',1049000 UNION
    SELECT 'EDB','af3',1059000  
    GO

    SELECT A.NAME,Y.* FROM #TT A CROSS JOIN (
    SELECT * FROM(
    SELECT CLASS,VALUE FROM #TT)X
    PIVOT(MAX(VALUE) FOR CLASS IN(AF1,AF2,AF3))AS PVT)Y
    GO

    SELECT * FROM #TT
    PIVOT(MAX(VALUE) FOR CLASS IN(AF1,AF2,AF3))AS PVT


    GO
    DROP TABLE #TT

    GO





    Wednesday, May 30, 2018 2:42 PM
  • I need the below table in pivot format

    the above query is not giving the required outcome

    sharing you the sample data

    Input

    Name

    Class Value
    CEF af1 1048000
    EDC af2

    1048000   

    OUTPUT

    Name af1 af2 af3
    CEF 1048000 1100 120000
    EDC 1048000 110000 124000

    can you explain where you got those values from?

    i.e 1100 120000 etc?

    Thats not shown in your sample data


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, May 30, 2018 3:18 PM
  • Hi Ychinnari,

    Since we can't see your actual data. Please share us the right sample data, so that we can provide accurate solutions.

    Below is my assumption, if there're only 'af1','af2','af3' in Class column. You can simply use PIVOT(). But if the values in Class column are dynamically changed. Then I would suggest you to use Dynamic Pivot().

    Please refer following scrip, hope it works for you:

    drop table #test
    
    create table #test
    (
    Name varchar(10),
    Class varchar(10),
    Val int
    )
    
    insert into #test values
    ('CEF','af1',1048000),
    ('CEF','af2',1100),
    ('CEF','af3',120000),
    ('EDC','af1',1048000),
    ('EDC','af2',110000),
    ('EDC','af3',124000)
    
    select *
    from (
    	select Name,Class,Val
    	from #test) t
    Pivot (Max(Val) for Class in ([af1],[af2],[af3])) pvt
    
    --Dynamic Pivot
    DECLARE @cols AS NVARCHAR(MAX),
        @sql  AS NVARCHAR(MAX);
    
    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.Class) 
                FROM #test t
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    --print @cols
    
    set @sql = 'SELECT Name, ' + @cols + ' from 
                (
                    select Name
                        , Class
                        , Val
                    from #test
               ) x
                pivot 
                (
                     max(Val)
                    for Class in (' + @cols + ')
                ) p '
    
    --print @sql
    execute(@sql)

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 31, 2018 2:38 AM