none
Merge columns

    Question

  • Hi,

    I have to compare data from 2 tables on 2 different servers with different formats.


    USE [TestDB]
    GO
    /****** Object:  Table [dbo].[Table_4]    Script Date: 11/20/2013 18:03:47 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Table_4](
    	[idno] [int] NULL,
    	[columns] [int] NULL,
    	[percentage] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (1, 1, 34)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (1, 2, 89)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 1, 45)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 2, 7)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 3, 12)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (3, 2, 5)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (3, 3, 18)
    /****** Object:  Table [dbo].[Table_3]    Script Date: 11/20/2013 18:03:47 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Table_3](
    	[idno] [int] NULL,
    	[column1] [int] NULL,
    	[column2] [int] NULL,
    	[column3] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (1, 23, 45, 0)
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (2, 28, 67, 5)
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (3, 12, 7, 67)
    

    Table_3

    idno

    column1

    column2

    column3

    1

    23

    45

    0

    2

    28

    67

    5

    3

    12

    7

    67

    The other table Table_4 looks like this

    idno

    Columns

    Percentage

    1

    1

    23

     1

    2

    45

    1

    3

    0

    2

    1

    28

    2

    2

    67

    2

    3

    5

    3

    1

    12

    3

    2

    7

    3

    3

    67

    I need to get table_3  in the same format as Table_4 so that I can compare the data from the 2 tables.

    I need to get idno and check if the value for column1 in table_3 is the same as percentage in table_4 for same idno where column=1. It will be easier to get the tables in the same format. I prefer getting table_3 in the same format as table_4.

    How can i get the same format? I need to use a temp table.

    Thanks

    Wednesday, November 20, 2013 4:07 PM

Answers

  • CREATE TABLE [dbo].[Table_4](
    	[idno] [int] NULL,
    	[columns] [int] NULL,
    	[percentage] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (1, 1, 34)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (1, 2, 89)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 1, 45)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 2, 7)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 3, 12)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (3, 2, 5)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (3, 3, 18)
    /****** Object:  Table [dbo].[Table_3]    Script Date: 11/20/2013 18:03:47 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Table_3](
    	[idno] [int] NULL,
    	[column1] [int] NULL,
    	[column2] [int] NULL,
    	[column3] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (1, 23, 45, 0)
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (2, 28, 67, 5)
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (3, 12, 7, 67)
    Select [idno],col,val from  [dbo].[Table_3]
    Cross apply (values(1,[column1]), (2,[column2]), (3,[column3])) d(col,val)
    Order bY [idno], col
    Select * from  [dbo].[Table_4]
    Order by [idno], [columns]
    drop table [dbo].[Table_3]
    drop table [dbo].[Table_4]

    Wednesday, November 20, 2013 4:35 PM
    Moderator
  • Thanks,

    Now my boss decided he wants it the other way round :-(

    That Table_4  will be in the same format as Table_3.

    Hi collie12,

    We can try the following commands:

    select [idno],[1] as [column1],[2] as [column2],[3] as [column3]
    from
    (
    SELECT [idno]
          ,[columns]
          ,[percentage]
      FROM [dbo].[Table_4]
    ) t
    pivot
    (
     max([percentage]) for [columns] in ([1],[2],[3])
    )
    as pvt

    Best Regards,
    Allen Li

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Friday, November 22, 2013 2:32 AM
    Moderator

All replies

  • See UNPIVOT.

    Regards.

    JN.


    Jean-Nicolas BERGER
    http://blog.sqlserver.fr


    Wednesday, November 20, 2013 4:10 PM
  • CREATE TABLE [dbo].[Table_4](
    	[idno] [int] NULL,
    	[columns] [int] NULL,
    	[percentage] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (1, 1, 34)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (1, 2, 89)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 1, 45)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 2, 7)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (2, 3, 12)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (3, 2, 5)
    INSERT [dbo].[Table_4] ([idno], [columns], [percentage]) VALUES (3, 3, 18)
    /****** Object:  Table [dbo].[Table_3]    Script Date: 11/20/2013 18:03:47 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Table_3](
    	[idno] [int] NULL,
    	[column1] [int] NULL,
    	[column2] [int] NULL,
    	[column3] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (1, 23, 45, 0)
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (2, 28, 67, 5)
    INSERT [dbo].[Table_3] ([idno], [column1], [column2], [column3]) VALUES (3, 12, 7, 67)
    Select [idno],col,val from  [dbo].[Table_3]
    Cross apply (values(1,[column1]), (2,[column2]), (3,[column3])) d(col,val)
    Order bY [idno], col
    Select * from  [dbo].[Table_4]
    Order by [idno], [columns]
    drop table [dbo].[Table_3]
    drop table [dbo].[Table_4]

    Wednesday, November 20, 2013 4:35 PM
    Moderator
  • Thanks,

    Now my boss decided he wants it the other way round :-(

    That Table_4  will be in the same format as Table_3.

    How?

    Thanks

    Thursday, November 21, 2013 1:30 PM
  • Thanks,

    Now my boss decided he wants it the other way round :-(

    That Table_4  will be in the same format as Table_3.

    Hi collie12,

    We can try the following commands:

    select [idno],[1] as [column1],[2] as [column2],[3] as [column3]
    from
    (
    SELECT [idno]
          ,[columns]
          ,[percentage]
      FROM [dbo].[Table_4]
    ) t
    pivot
    (
     max([percentage]) for [columns] in ([1],[2],[3])
    )
    as pvt

    Best Regards,
    Allen Li

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Friday, November 22, 2013 2:32 AM
    Moderator
  • Hi,

    Thank you :)

    Saturday, December 14, 2013 6:15 PM