Post reply

Create a table from another table for multiple values

  • May 3, 2010 at 1:06 am

    #224089

    I have table with data that has multiple values for some columns, I need to make that values of rows to be the columns of the table.

    The Fields of the first table is:

    CUST_NO,

    DOC_DATE,

    DOC_TYPE,

    DOC_NO,

    Line,

    Amt

    in which I have rows with same data except Line and amt.

    I need to create a table to avoid the duplication of the data and need to create a table like:

    CUST_NO,

    DOC_DATE,

    DOC_TYPE,

    DOC_NO,

    Line1,

    Amt1

    Line2,

    Amt2,

    Line3,

    Amt3,

    Line4,

    Amt4,

    Line5,

    Amt5

    in which the maximum number of Line and Amt is 5.

    The sample data :

    QUTE00,13/4/2010 0:00,I,D38928-703,100,patiriciaption in Centre1 ,100,patiriciaption in Centre1 ,100,patiriciaption in Centre1 ,100,patiriciaption in Centre1 ,100

    QUTE00,13/4/2010 0:00,I,D38928-703,200,patiriciaption in Centre2,200,patiriciaption in Centre2,200,patiriciaption in Centre2,200,patiriciaption in Centre2,200

    SWUT00,13/4/2010 0:00,I,D38932-703,500,patiriciaption in Centre3,500,patiriciaption in Centre3,500,patiriciaption in Centre3,500,patiriciaption in Centre3,500

    SWUT00,13/4/2010 0:00,I,D38932-703,500,patiriciaption in Centre4,500,patiriciaption in Centre4,500,patiriciaption in Centre4,500,patiriciaption in Centre4,500

    DUNI00,14/4/2010 0:00,I,D39006-703,200,patiriciaption in Centre5,200,patiriciaption in Centre5,200,patiriciaption in Centre5,200,patiriciaption in Centre5,200

    DUNI00,14/4/2010 0:00,I,D39006-703,200,patiriciaption in Centre6,200,patiriciaption in Centre6,200,patiriciaption in Centre6,200,patiriciaption in Centre6,200

    RMIT00,14/4/2010 0:00,I,D39007-703,50,patiriciaption in Centre7,50,patiriciaption in Centre7,50,patiriciaption in Centre7,50,patiriciaption in Centre7,50

    RMIT00,14/4/2010 0:00,I,D39007-703,20,patiriciaption in Centre8,20,patiriciaption in Centre8,20,patiriciaption in Centre8,20,patiriciaption in Centre8,20

    MURU00,14/4/2010 0:00,I,D39011-703,40,patiriciaption in Centre9,40,patiriciaption in Centre9,40,patiriciaption in Centre9,40,patiriciaption in Centre9,40

    MURU00,14/4/2010 0:00,I,D39011-703,40,patiriciaption in Centre10,40,patiriciaption in Centre10,40,patiriciaption in Centre10,40,patiriciaption in Centre10,40

    ICHM00,14/4/2010 0:00,I,D39013-733,12,patiriciaption in Centre11,12,patiriciaption in Centre11,12,patiriciaption in Centre11,12,patiriciaption in Centre11,12

    Pleae help.

  • May 3, 2010 at 5:31 am

    #1161193

    It's called CroosTab or Pivot.

    For a CrossTab solution please see the related link in my signature. It will shortly describe the PIVOT syntax as well.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 3, 2010 at 7:43 pm

    #1161567

    Hi ,

    Its true that its sort of pivot table .

    but in my case, I cant predefine the number of occurance of the Line and amt and so how can i use PIVOT in that case?

    So If I use Pivot , how can I use any function or criteria? In my case, there is special criteria or reason or value that I can take into to create those.

    It will have atleast one value for sure, and can have maximum value.

    So I am not sure how can i use this function.

    so please help me...

    is this ok to post here or do i need to post it in any other area?

  • May 4, 2010 at 10:24 am

    #1161931

    Did you actually read the CrossTab article I pointed you at? It will answer most of your questions ,except for the variable number of columns.

    Once you understood the CroosTab concept you should go to the "next level2: DynamicCrossTab (again, linked in my signature). This article then will help you to build a Pivot table with an varying number of columns.

    And yes, it's ok to post here (it's not really security related, but the thread will be recognized...). 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 10, 2010 at 7:48 pm

    #1165296

    Hi, I have gone through the articles and tried. But the cross tab is not working for me.

    please help.

    Thanks

  • May 11, 2010 at 10:36 am

    #1165683

    Please provide the table definition (DDL script), some sample data (INSERT ...SELECT), your expected output related to the sample data, what you've tried so far and where you get stuck.

    For a detailed description on how to post sample data please see the first link in my signature.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 24, 2010 at 7:26 pm

    #1171825

    Please find herewith the table and the data for this request.

    CREATE TABLE [dbo].#[RealTable](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [DOC_NO] [varchar](50) NULL,

    [DOC_DATE] [datetime] NULL,

    [DOC_TYPE] [varchar](255) NULL,

    [CUST_NO] [varchar](255) NULL,

    [Line] [varchar](255) NULL,

    [Amt] [decimal](13, 2) NULL

    ) ON [PRIMARY]

    ===========

    SELECT 'WATA00','Jan 1 1900 12:00AM','C','D38477-733','particiaption in Event','120.00', UNION ALL

    SELECT 'WATA00','Jan 1 1900 12:00AM','C','D38477-733','exam','230.00', UNION ALL

    SELECT 'QTAF04','Jan 1 1900 12:00AM','T','D38478-730','particiaption in Event','50.00', UNION ALL

    SELECT 'MELU00','Jan 1 1900 12:00AM','C','D38479-714','particiaption in Event','180.00', UNION ALL

    SELECT 'HOTS00','Jan 1 1900 12:00AM','T','D38480-733','particiaption in Event','210.00', UNION ALL

    SELECT 'DUNI01','Jan 1 1900 12:00AM','C','D38724-714','particiaption in Event','240.00', UNION ALL

    SELECT 'NTUN00','Jul 4 2009 12:00AM','T','D38725-714','particiaption in Event','270.00', UNION ALL

    SELECT 'GUNI00','Aug 4 2009 12:00AM','C','D38726-730','particiaption in Event','300.00', UNION ALL

    SELECT 'MELU00','Sep 4 2009 12:00AM','T','D38727-733','particiaption in Event','30.00', UNION ALL

    SELECT 'MURD00','Oct 4 2009 12:00AM','C','D38728-733','particiaption in Event','60.00', UNION ALL

    SELECT 'CICO00','Nov 4 2009 12:00AM','T','D38729-733','particiaption in Event','390.00', UNION ALL

    SELECT 'CUTE00','Dec 4 2009 12:00AM','C','D38730-733','particiaption in Event','420.00', UNION ALL

    SELECT 'WATA00','Jan 1 1900 12:00AM','C','D38477-733','particiaption in Event','120.00', UNION ALL

    SELECT 'QTAF04','Sep 1 2009 12:00AM','T','D38478-730','particiaption in Event','50.00', UNION ALL

    SELECT 'MELU00','Sep 1 2009 12:00AM','C','D38479-714','particiaption in Event','180.00', UNION ALL

    SELECT 'HOTS00','Sep 1 2009 12:00AM','T','D38480-733','particiaption in Event','210.00', UNION ALL

    SELECT 'DUNI01','Sep 1 2009 12:00AM','C','D38724-714','particiaption in Event','240.00', UNION ALL

    SELECT 'NTUN00','Sep 1 2009 12:00AM','T','D38725-714','particiaption in Event','270.00', UNION ALL

    SELECT 'GUNI00','Sep 1 2009 12:00AM','C','D38726-730','particiaption in Event','300.00', UNION ALL

    SELECT 'MELU00','Sep 1 2009 12:00AM','T','D38727-733','particiaption in Event','30.00', UNION ALL

    SELECT 'MURD00','Sep 1 2009 12:00AM','C','D38728-733','particiaption in Event','60.00', UNION ALL

    SELECT 'CICO00','Sep 1 2009 12:00AM','T','D38729-733','particiaption in Event','390.00', UNION ALL

    SELECT 'CUTE00','Sep 1 2009 12:00AM','C','D38730-733','particiaption in Event','420.00', UNION ALL

    SELECT 'IEDS00','Sep 1 2009 12:00AM','T','D38731-764','particiaption in Event','450.00', UNION ALL

    SELECT 'UNSW00','Sep 1 2009 12:00AM','C','D38732-764','particiaption in Event','80.00', UNION ALL

    SELECT 'SGAU00','Sep 1 2009 12:00AM','T','D38733-764','particiaption in Event','510.00', UNION ALL

    SELECT 'QIBT00','Sep 1 2009 12:00AM','C','D38734-764','particiaption in Event','540.00', UNION ALL

    SELECT 'TRIN00','Sep 1 2009 12:00AM','T','D38735-764','particiaption in Event','570.00', UNION ALL

    SELECT 'TRIN00','Sep 1 2009 12:00AM','C','D38735-764','particiaption in course','600.00', UNION ALL

    SELECT 'SWUT00','Sep 1 2009 12:00AM','T','D38932-703','particiaption in Event','630.00', UNION ALL

    SELECT 'SWUT00','Sep 1 2009 12:00AM','T','D38932-703','particiaption in exam','690.00'

    =========

    the new table:

    CREATE TABLE [dbo].[TestTable](

    [DOC_NO] [varchar](50) NULL,

    [DOC_DATE] [datetime] NULL,

    [DOC_TYPE] [varchar](255) NULL,

    [CUST_NO] [varchar](255) NULL,

    [Line1] [varchar](255) NULL,

    [Amt1] [decimal](13, 2) NULL,

    [Line2] [varchar](21) NULL,

    [Amt2] [decimal](13, 2) NULL,

    [Line3] [varchar](21) NULL,

    [Amt3] [decimal](13, 2) NULL,

    [Line4] [varchar](21) NULL,

    [Amt4] [decimal](13, 2) NULL,

    [Line5] [varchar](21) NULL,

    [Amt5] [decimal](13, 2) NULL

    ) ON [PRIMARY]

    ============

    i need a table for the first and last rows as follows ( the condiditon is all the values in the row should be identical except the Lin and Amt to make it as a new column to that row, HERE THE FIRST TWO AND LAST TWO ARE IDENTICAL) and all other rows with same data where line and amt data will be added to line1 and amt1 columns.:

    SELECT 'WATA00','Jan 1 1900 12:00AM','C','D38477-733','particiaption in Event','120.00','exam','230.00'

    SELECT 'SWUT00','Sep 1 2009 12:00AM','T','D38932-703','particiaption in Event','630.00', 'particiaption in exam','690.00'

    Hope this will help ...

    thankss

  • May 25, 2010 at 12:50 pm

    #1172310

    Here are some logic related questions:

    What's the business rule for selecting the rows to show up in your final table?

    Is it guaranteed that you'll never have more than 5 lines (in your example you have a max of 2)? What if you have more than 5?

    What is the rule to define the order of lines (ordered by id?)?

    How to deal with exact duplicates (example see below)?

    Your sample data and your expected result don't match as far as I can see...

    Some examples:

    1) Why isn't id 13 used to add up particiaption in Event to 240 instead of 120?

    2) Why is TRIN00 (id 28 and 29) not part of your final output?

    3) Why are the other DOC_NO excluded?

    Right now I can't find any reason not to use a static CrossTab soltion (since you pre-defined the TestTable with a max of 5 lines).

    Please clarify.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

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