Post reply

rows to columns

  • May 11, 2012 at 8:49 am

    #255812

    I have 2 tables:

    dbo.Doc_activity and dbo.event_type

    CREATE TABLE [dbo].[Doc_Activity](

    [Entry_Id] [int] NOT NULL,

    [Event_Type_Id] [int] NOT NULL,

    [Event_Value] [varchar](1023) NULL,

    [Event_Time] [datetime] NOT NULL,

    [User_Name_Event] [varchar](200) NULL,

    [Last_Updated_User] [varchar](1023) NULL,

    [Last_Updated] [datetime] NULL

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[Event_Type](

    [Event_Description] [varchar](50) NULL,

    [Event_Type_Id] [int] NOT NULL)

    I know what I did is not right..but just to explain what I need, writing below query:

    CREATE TABLE #TEMPOUT( sys_Routing_Action VARCHAR(200), sys_WkfStep_Current VARCHAR(200))

    insert into #TEMPOUT (sys_Routing_Action,sys_WkfStep_Current)values((select Event_Value from dbo.Doc_Activity doc

    inner join dbo.Event_Type ET

    on Doc.Event_Type_Id= ET.Event_Type_Id

    where ET.Event_Type_Id = 4),(select Event_Value from dbo.Doc_Activity doc

    inner join dbo.Event_Type ET

    on Doc.Event_Type_Id= ET.Event_Type_Id

    where ET.Event_Type_Id = 5))

  • May 11, 2012 at 8:54 am

    #1486362

    What are you trying to achieve - what results are you expecting?

    Could you post some sample data?

    Andy

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • May 11, 2012 at 9:01 am

    #1486369

    komal145 (5/11/2012)

    I have 2 tables:

    dbo.Doc_activity and dbo.event_type

    CREATE TABLE [dbo].[Doc_Activity](

    [Entry_Id] [int] NOT NULL,

    [Event_Type_Id] [int] NOT NULL,

    [Event_Value] [varchar](1023) NULL,

    [Event_Time] [datetime] NOT NULL,

    [User_Name_Event] [varchar](200) NULL,

    [Last_Updated_User] [varchar](1023) NULL,

    [Last_Updated] [datetime] NULL

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[Event_Type](

    [Event_Description] [varchar](50) NULL,

    [Event_Type_Id] [int] NOT NULL)

    I know what I did is not right..but just to explain what I need, writing below query:

    CREATE TABLE #TEMPOUT( sys_Routing_Action VARCHAR(200), sys_WkfStep_Current VARCHAR(200))

    insert into #TEMPOUT (sys_Routing_Action,sys_WkfStep_Current)values((select Event_Value from dbo.Doc_Activity doc

    inner join dbo.Event_Type ET

    on Doc.Event_Type_Id= ET.Event_Type_Id

    where ET.Event_Type_Id = 4),(select Event_Value from dbo.Doc_Activity doc

    inner join dbo.Event_Type ET

    on Doc.Event_Type_Id= ET.Event_Type_Id

    where ET.Event_Type_Id = 5))

    forjust 1 row returned this is easy - how many rows do you expect - if 1 then the following

    insert into #TEMPOUT (sys_Routing_Action,sys_WkfStep_Current)

    select t1.Event_Value, t2.event_value from dbo.Doc_Activity doc t2

    inner join dbo.Event_Type t1 on

    on t1.Event_Type_Id=4 and t2.ET.Event_Type_Id=5

    MVDBA

  • May 11, 2012 at 9:08 am

    #1486377

    hi

    event_type table has values

    Event_DescriptionEvent_Type_Id

    Status 1

    foldername 2

    sys_Routing_Action3

    sys_WkfStep_Previous4

    sys_WkfStep_Current5

    doc_activity table has below values

    Entry_Id Event_Type_IdEvent_ValueEvent_Time

    915Indexing 51:53.2

    914Intake51:53.2WOODLAND_HILLS\chris.johnson

    so I want two columns called sys_Routing_Action , sys_WkfStep_Current

  • May 11, 2012 at 9:20 am

    #1486385

    and how many results do you expect ? - can you post what you expect the final data to look like - because with only 2 columns in your table i can only guess you are looking for one row of data to be returned

    MVDBA

  • May 11, 2012 at 9:36 am

    #1486403

    entry_id sys_Routing_Action sys_WkfStep_Current

    123 Indexing Intake

  • May 11, 2012 at 9:41 am

    #1486406

    komal145 (5/11/2012)

    entry_id sys_Routing_Action sys_WkfStep_Current

    123 Indexing Intake

    ok - i don't wish to be rude, but a little clearer and more verbose information helps me to help you... we give up our free time to help out and you're not even being polite and using basic words or saying please/thankyou

    this should give the solution you have "described"

    insert into #TEMPOUT (entry_id,sys_Routing_Action,sys_WkfStep_Current)

    select t1.Event_Value, t2.event_value from dbo.Doc_Activity doc t2

    inner join dbo.Event_Type t1 on

    on t1.Event_Type_Id=4 and t2.ET.Event_Type_Id=5 and t1.entry_id=t2.entry_id

    MVDBA

  • May 11, 2012 at 11:18 am

    #1486474

    sorry !! i am always thankful to the people for helping me out.

    due to few situations..i am just copying from notepad what I typed.

    Thank you , I worked out the solution you gave me but getting multiple records for single entry_id.

    drop table #TEMPOUT

    CREATE TABLE #TEMPOUT( entry_id int,sys_Routing_Action VARCHAR(200), sys_WkfStep_Current VARCHAR(200))

    insert into #TEMPOUT (entry_id,sys_Routing_Action,sys_WkfStep_Current)

    select

    LFMA.Entry_id,

    t1.Event_Value,

    t2.event_value

    from dbo.Doc_Activity t2

    inner join dbo.Doc_Activity t1 on t1.Event_Type_Id=3 and t2.Event_Type_Id=5

    inner join dbo.LF_MidAtlantic_All_Entries LFMA on LFMA.entry_id = t2.entry_id

    I am trying to get the current_step for that Routing_action for taht entry_id, which i am not getting

    Thank you.I appreciate you help.

  • May 11, 2012 at 11:23 am

    #1486477

    Sample data for your tables and the expected results would go a long way to helping you.

    The sample data should be using INSERT INTO statements for the tables so that we just cut, paste, execute to populate the tables.

  • May 11, 2012 at 11:55 am

    #1486501

    hi ,

    Insert into dbo.Doc_Activity (Entry_Id,Event_Type_Id,Event_Value,Event_Time,User_Name_Event,Last_Updated_User,Last_Updated)

    values ( 91,5, 'Indexing',2012-04-23 15:51:53.177,asdf,asdf,2012-04-23 15:51:53.177)

    Insert into dbo.Doc_Activity (Entry_Id,Event_Type_Id,Event_Value,Event_Time,User_Name_Event,Last_Updated_User,Last_Updated)

    values ( 91,4, 'Intake',2012-04-23 15:51:53.177,asdf,asdf,2012-04-23 15:51:53.177)

    Insert into dbo.Doc_Activity (Entry_Id,Event_Type_Id,Event_Value,Event_Time,User_Name_Event,Last_Updated_User,Last_Updated)

    values ( 298,5, 'Indexing',2012-05-05 23:31:22.660,asdf,asdf,2012-04-23 15:51:53.177)

    Insert into dbo.Doc_Activity (Entry_Id,Event_Type_Id,Event_Value,Event_Time,User_Name_Event,Last_Updated_User,Last_Updated)

    values ( 298,4, 'Intake',2012-05-05 23:31:22.660,asdf,asdf,2012-04-23 15:51:53.177)

    Insert into dbo.event_type ( event_description , event_type_id) values( 'sys_Routing_Action',3)

    Insert into dbo.event_type ( event_description , event_type_id) values( 'sys_WkfStep_Current',5)

    thanks,

    komal

  • May 11, 2012 at 1:01 pm

    #1486564

    komal145 (5/11/2012)

    hi ,

    Insert into dbo.Doc_Activity (Entry_Id,Event_Type_Id,Event_Value,Event_Time,User_Name_Event,Last_Updated_User,Last_Updated)

    values ( 91,5, 'Indexing',2012-04-23 15:51:53.177,asdf,asdf,2012-04-23 15:51:53.177)

    Insert into dbo.Doc_Activity (Entry_Id,Event_Type_Id,Event_Value,Event_Time,User_Name_Event,Last_Updated_User,Last_Updated)

    values ( 91,4, 'Intake',2012-04-23 15:51:53.177,asdf,asdf,2012-04-23 15:51:53.177)

    Insert into dbo.Doc_Activity (Entry_Id,Event_Type_Id,Event_Value,Event_Time,User_Name_Event,Last_Updated_User,Last_Updated)

    values ( 298,5, 'Indexing',2012-05-05 23:31:22.660,asdf,asdf,2012-04-23 15:51:53.177)

    Insert into dbo.Doc_Activity (Entry_Id,Event_Type_Id,Event_Value,Event_Time,User_Name_Event,Last_Updated_User,Last_Updated)

    values ( 298,4, 'Intake',2012-05-05 23:31:22.660,asdf,asdf,2012-04-23 15:51:53.177)

    Insert into dbo.event_type ( event_description , event_type_id) values( 'sys_Routing_Action',3)

    Insert into dbo.event_type ( event_description , event_type_id) values( 'sys_WkfStep_Current',5)

    thanks,

    komal

    OK so your sample data is full of errors. Assuming I fix them, what should the output be based on this sample data?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 12, 2012 at 9:39 am

    #1486759

    The following article explains a couple of methods to change rows to columns. For character data, use MAX instead of SUM.

    [font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

    [/font][/url]

    --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 12 posts - 1 through 11 (of 11 total)

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