Merge columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Merge columns

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-11-20 : 11:10:49

[code]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)

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 34
1 2 89
2 1 45
2 2 7
2 3 12
3 2 5
3 3 18
[/code]

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 11:32:47

Use unpivot. You can also do the same without unpivoting, which is probably just as easy

SELECT * FROM Table_3
UNPIVOT (percentage FOR [col] IN ([column1],[column2],[column3]))U

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-11-20 : 11:53:25

Thanks . Does what i need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources