Pivoting the table

  • I need some help in pivoting the table.

    The table is like this:

    File Main Sub Text

    1 1 A. hello

    1 1 B. SQL

    5 1 A. central

    5 1 B. .com

    The modified table look like this

    File Main A. B.

    1 1 hello SQL

    5 1 central .com

    All text rows related to A come under column A and same B.

    i tried a lot with different Pivoting queries but i'm getting either NULLS or empty fields.

    Can anyone suggest me how to get this.

    Thanks in Advance!

  • Hi

    will you only "ever" have columns A and B to pivot on?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • If it fixed number of columns classic cross tab should work:

    CREATE TABLE #Temp([File] INT, Main INT, Sub CHAR(2), [Text] VARCHAR(20));

    INSERT INTO #Temp ([File], Main, Sub, [Text])

    VALUES (1, 1, 'A.', 'hello'),

    (1, 1, 'B.', 'SQL'),

    (5, 1, 'A.', 'central'),

    (5, 1, 'B.', '.com');

    SELECT

    [File]

    ,Main

    ,[A.] = MAX(CASE WHEN Sub = 'A.' THEN [Text] END)

    ,[B.] = MAX(CASE WHEN Sub = 'B.' THEN [Text] END)

    FROM #Temp

    GROUP BY [File], Main;

    DROP TABLE #Temp;

    --Vadim R.

  • Yes, I have only two group of values "A" and "B" need to be pivoted

  • ashwinrao.k23 (8/10/2012)


    Yes, I have only two group of values "A" and "B" need to be pivoted

    ok...in which case the solution from rVadim will work for you.

    for more info, suggest you search under the "Authors" (left sidebar) for Jeff Moden and his articles on crosstabs and pivots

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • thanks for the Reply!

    If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..

  • ashwinrao.k23 (8/10/2012)


    thanks for the Reply!

    If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..

    not quite sure what data fields are being "eaten"....maybe if you provide a script of the table....in the format that rVadim set out above, with a representative sample of data that demonstrates your problem (and expected results please)...then I am sure someone will be able to help

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Exactly!

    If sample you provided doesn't accurately reflect you actual situation then you need to provide a better sample.

    People will be more willing to help if you include CREATE TABLE and INSERT statement to generate sample data.

    --Vadim R.

  • ashwinrao.k23 (8/10/2012)


    thanks for the Reply!

    If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..

    hmm...just a thought, but by any chance do you have data like this (based on your original description)

    The table is like this:

    File Main Sub Text

    1 1 A. hello

    1 1 B. SQL

    5 1 A. central

    5 1 B. .com

    1 1 A. hello_again

    1 1 B. SQL_again

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • No. all fields will not repeat as same. Text may be same but File and Main will be different..

  • Combination of File + Main + Sub should be unique. Is that so?

    --Vadim R.

  • Here is the scenario please..

    For exmaple if i have the fields like below

    File Main Sub Text

    917 2 A. Text1

    917 2 B. Text2

    917 2 A. Text3

    917 2 B. Text4

    917 2 A. Text5

    917 2 B. Text6

    If i use Aggregate function the result is only 1 field under A and B column which are maximum of available rows.

    but i need to get all fields.

  • ashwinrao.k23 (8/10/2012)


    Here is the scenario please..

    For exmaple if i have the fields like below

    File Main Sub Text

    917 2 A. Text1

    917 2 B. Text2

    917 2 A. Text3

    917 2 B. Text4

    917 2 A. Text5

    917 2 B. Text6

    If i use Aggregate function the result is only 1 field under A and B column which are maximum of available rows.

    but i need to get all fields.

    ok...so pls provide the results you want based on the data you have provided

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • i need something like.

    File Main A B

    917 2 Text1 Text2

    917 2 Text3 Text4

    917 2 Text5 Text6

  • I can do it for given dataset but generally speaking how would you know which TextA should be paired with which TextB? I do it simply by including [Text] in the order by, which works for the given setup. Does it matter that Test1 goes with Text2? Can Text1 be paired with Text6, for example?

    CREATE TABLE #Temp([File] INT, Main INT, Sub CHAR(2), [Text] VARCHAR(20));

    INSERT INTO #Temp ([File], Main, Sub, [Text])

    VALUES (917, 2, 'A.', 'Text1'),

    (917, 2, 'B.', 'Text2'),

    (917, 2, 'A.', 'Text3'),

    (917, 2, 'B.', 'Text4'),

    (917, 2, 'A.', 'Text5'),

    (917, 2, 'B.', 'Text6')

    --SELECT

    -- [File]

    -- ,Main

    -- ,[A.] = MAX(CASE WHEN Sub = 'A.' THEN [Text] END)

    -- ,[B.] = MAX(CASE WHEN Sub = 'B.' THEN [Text] END)

    --FROM #Temp

    --GROUP BY [File], Main

    ;WITH TempCTE AS (

    SELECT

    [File], Main, Sub, [Text]

    ,RowNum = ROW_NUMBER() OVER (PARTITION BY [File], Main, Sub ORDER BY [File], Main, Sub, [Text])

    FROM #Temp

    )

    SELECT

    [File]

    ,Main

    ,[A.] = MAX(CASE WHEN Sub = 'A.' THEN [Text] END)

    ,[B.] = MAX(CASE WHEN Sub = 'B.' THEN [Text] END)

    FROM TempCTE

    GROUP BY [File], Main, RowNum

    DROP TABLE #Temp;

    --Vadim R.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply