Post reply

Pivoting the table

  • August 13, 2012 at 7:36 am

    #1525047

    Thank you very much rVadim!

    your query working pretty good.. but mispairing in A and B. Sorry I did'nt provided that info..

    TextA should be pair with TextB which comes next to it. Like Text1-Text2,Text3-Text4 and 5-6.

    Other than this your query is good.Can you modify the query to get above requirement..

  • August 13, 2012 at 8:19 am

    #1525065

    Do you have any column that identifies the ordering of the [Text] column within the [File],Main columns, or is the [Text] column to be sorted alphabetically?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 13, 2012 at 9:21 am

    #1525115

    Here is a solution for your test data. However, it doesn't address the question of how to control the ordering of the [Text] column. Does this table have an identity column or something else that controls this?

    WITH cteData AS

    (

    -- put the test data into a CTE

    SELECT [File], Main, Sub, [Text]

    FROM (

    -- modified test data

    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'),

    -- initial test data

    (1 , 1, 'A.', 'Hello'),

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

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

    (5 , 1, 'B.', 'com') ) Data ([File], Main, Sub, [Text])

    ), cteDataRN AS

    (

    -- add a row number (RN), ordering by [Text]

    -- this assumes that the ordering in performed by the [Text] column

    -- however, if the [Text] column isn't how the ordering is to be performed,

    -- then this needs to be modified. Perhaps an identity column?

    SELECT [File], Main, Sub, [Text],

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

    FROM cteData

    ), cteDataGrp AS

    (

    -- add a grouping number to group related rows together in pairs

    SELECT [File], Main, Sub, [Text], RN,

    Grp = CEILING(RN/2.0)

    FROM cteDataRN

    )

    SELECT [File], Main,

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

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

    FROM cteDataGrp

    GROUP BY [File], Main, Grp;

    My results:

    File Main A B

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

    1 1 Hello SQL

    5 1 central com

    917 2 Text1 Text2

    917 2 Text3 Text4

    917 2 Text5 Text6

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 14, 2012 at 3:25 am

    #1525406

    DECLARE @Temp TABLE ([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;

  • August 14, 2012 at 9:07 pm

    #1525863

    Kumar SQL (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.

    Then there's really no sure way to do this because you have nothing to guarantee the order of the rows and you have no sure way to guarantee that Text1 and Text2 (for example) are the two items that need to go together. Do you have another column to identify the correct order of the rows?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 19 (of 19 total)

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