quote:

---

Originally posted by X002548

a View should do it

Please post the DDL of the tables, and some sample DML data examples to insert into thos tables
Brett
8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

---

quote:

---

Originally posted by revdnrdy

[quote]Originally posted by X002548
Why would you want to use a view on a production database? This could create a performance hit due to IO usage. At my company we only use views for querying against a reporting server..

---

quote:

---

Originally posted by jim_jim

can you please advise how this can be accomplished by remaping the data from A to B
Iam thinking of some logic but not sure how to proceed by remaping data from A to B

Please advise

---

Rid PlatA PlatB PlatC PlatD Policy    A1  Policyno  A2    
133 Yes    NULL  NULL  NULL  98781  98782  98783   98784 
134 NULL    Yes  NULL  NULL  NULL    NULL  NULL     NULL
135 NULL   NULL  Yes   NULL  NULL    NULL  NULL     NULL 
136 NULL   NULL  NULL  Yes   NULL    NULL  NULL     NULL

quote:

---

Do yoy know what you're talkin about?
Or are you just parroting what some mucky muck told you?

---

CREATE TABLE myTableA99 (
		  RequestID		int IDENTITY(1,1)
		, [Platform A]	varchar(50)
		, [Platform B]	varchar(50)
		, [Platform C]	varchar(50)
		, [Platform D]	varchar(50)
		, PolicynoA1	varchar(50)
		, PolicynoA2	varchar(50)
		, PolicynoA3	varchar(50)
		, PolicynoA4	varchar(50)
		, PolicynoA5	varchar(50)
		, PolicynoB1	varchar(50)
		, PolicynoB2	varchar(50)
		, PolicynoB3	varchar(50)
		, PolicynoB4	varchar(50)
		, PolicynoB5	varchar(50)
		, PolicynoC1	varchar(50)
		, PolicynoC2	varchar(50)
		, PolicynoC3	varchar(50)
		, PolicynoC4	varchar(50)
		, PolicynoC5	varchar(50)
		, PolicynoD1	varchar(50)
		, PolicynoD2	varchar(50)
		, PolicynoD3	varchar(50)
		, PolicynoD4	varchar(50)
		, PolicynoD5	varchar(50)
)
GO

CREATE TABLE myTableB99 (
		  [RequestID]		int
		, [RequestSubID]	int IDENTITY(1,1)
		, [Platform]		varchar(50)
		, [Policy]			varchar(50)
		, [xGrid]			char(1)
		, [yGrid]			char(1)
)
GO

INSERT INTO myTableA99(
--		  RequestID		int IDENTITY(1,1)
		  [Platform A]
		, [Platform B]
		, [Platform C]
		, [Platform D]
		, PolicynoA1
		, PolicynoA2
		, PolicynoA3
		, PolicynoA4
		, PolicynoA5
		, PolicynoB1
		, PolicynoB2
		, PolicynoB3
		, PolicynoB4
		, PolicynoB5
		, PolicynoC1
		, PolicynoC2
		, PolicynoC3
		, PolicynoC4
		, PolicynoC5
		, PolicynoD1
		, PolicynoD2
		, PolicynoD3
		, PolicynoD4
		, PolicynoD5
)
SELECT 'A1', 'B1', 'C1', 'D1'
		, 'PolicynoA1A', 'PolicynoA2A', 'PolicynoA3A', 'PolicynoA4A', 'PolicynoA5A'
		, 'PolicynoB1B', 'PolicynoB2B', 'PolicynoB3B', 'PolicynoB4B', 'PolicynoB5B'
		, 'PolicynoC1C', 'PolicynoC2C', 'PolicynoC3C', 'PolicynoC4C', 'PolicynoC5C'
		, 'PolicynoD1D', 'PolicynoD2D', 'PolicynoD3D', 'PolicynoD4D', 'PolicynoD5D'
UNION ALL
SELECT 'A2', 'B2', 'C2', 'D2'
		, 'PolicynoA1W', 'PolicynoA2W', 'PolicynoA3W', 'PolicynoA4W', 'PolicynoA5W'
		, 'PolicynoB1X', 'PolicynoB2X', 'PolicynoB3X', 'PolicynoB4X', 'PolicynoB5X'
		, 'PolicynoC1Y', 'PolicynoC2Y', 'PolicynoC3Y', 'PolicynoC4Y', 'PolicynoC5Y'
		, 'PolicynoD1Z', 'PolicynoD2Z', 'PolicynoD3Z', 'PolicynoD4Z', 'PolicynoD5Z'
GO


-- Noramalize Data

INSERT INTO myTableB99 (
		  [RequestID]
--		, [RequestSubID]
		, [Platform]
		, [Policy]
		, [xGrid]
		, [yGrid]
		)
SELECT 	  RequestID, [Platform A], PolicynoA1, 'A', '1'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform A], PolicynoA2, 'A', '2'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform A], PolicynoA3, 'A', '3'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform A], PolicynoA4, 'A', '4'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform A], PolicynoA5, 'A', '5'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform B], PolicynoB1, 'B', '1'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform B], PolicynoB2, 'B', '2'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform B], PolicynoB3, 'B', '3'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform B], PolicynoB4, 'B', '4'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform B], PolicynoB5, 'B', '5'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform C], PolicynoC1, 'C', '1'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform C], PolicynoC2, 'C', '2'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform C], PolicynoC3, 'C', '3'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform C], PolicynoC4, 'C', '4'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform C], PolicynoC5, 'C', '5'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform D], PolicynoD1, 'D', '1'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform D], PolicynoD2, 'D', '2'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform D], PolicynoD3, 'D', '3'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform D], PolicynoD4, 'D', '4'  FROM myTableA99 UNION ALL
SELECT 	  RequestID, [Platform D], PolicynoD5, 'D', '5'  FROM myTableA99
GO

SELECT * FROM myTableA99
GO

SELECT * FROM myTableB99
GO

EXEC sp_rename 'myTableA99', 'myTableA99_bkp'
GO

SELECT * FROM myTableA99_bkp
GO

CREATE VIEW myTableA99
AS
SELECT A.RequestID, [Platform A], [Platform B], [Platform C], [Platform D]
	, MAX(PolicynoA1) AS PolicynoA1
	, MAX(PolicynoA2) AS PolicynoA2
	, MAX(PolicynoA3) AS PolicynoA3
	, MAX(PolicynoA4) AS PolicynoA4
	, MAX(PolicynoA5) AS PolicynoA5
	, MAX(PolicynoB1) AS PolicynoB1
	, MAX(PolicynoB2) AS PolicynoB2
	, MAX(PolicynoB3) AS PolicynoB3
	, MAX(PolicynoB4) AS PolicynoB4
	, MAX(PolicynoB5) AS PolicynoB5
	, MAX(PolicynoC1) AS PolicynoC1
	, MAX(PolicynoC2) AS PolicynoC2
	, MAX(PolicynoC3) AS PolicynoC3
	, MAX(PolicynoC4) AS PolicynoC4
	, MAX(PolicynoC5) AS PolicynoC5
	, MAX(PolicynoD1) AS PolicynoD1
	, MAX(PolicynoD2) AS PolicynoD2
	, MAX(PolicynoD3) AS PolicynoD3
	, MAX(PolicynoD4) AS PolicynoD4
	, MAX(PolicynoD5) AS PolicynoD5
  FROM    (
		  SELECT  RequestID, [Platform] AS [Platform A]
				, CASE WHEN yGrid = '1' THEN [Policy] END AS PolicynoA1
				, CASE WHEN yGrid = '2' THEN [Policy] END AS PolicynoA2
				, CASE WHEN yGrid = '3' THEN [Policy] END AS PolicynoA3
				, CASE WHEN yGrid = '4' THEN [Policy] END AS PolicynoA4
				, CASE WHEN yGrid = '5' THEN [Policy] END AS PolicynoA5
			FROM myTableB99
		   WHERE xGrid = 'A') AS A
  JOIN   (
		  SELECT  RequestID, [Platform] AS [Platform B]
				, CASE WHEN yGrid = '1' THEN [Policy] END AS PolicynoB1
				, CASE WHEN yGrid = '2' THEN [Policy] END AS PolicynoB2
				, CASE WHEN yGrid = '3' THEN [Policy] END AS PolicynoB3
				, CASE WHEN yGrid = '4' THEN [Policy] END AS PolicynoB4
				, CASE WHEN yGrid = '5' THEN [Policy] END AS PolicynoB5
			FROM myTableB99
		   WHERE xGrid = 'B') AS B
	ON A.requestID = B.RequestID
  JOIN   (
		  SELECT  RequestID, [Platform] AS [Platform C]
				, CASE WHEN yGrid = '1' THEN [Policy] END AS PolicynoC1
				, CASE WHEN yGrid = '2' THEN [Policy] END AS PolicynoC2
				, CASE WHEN yGrid = '3' THEN [Policy] END AS PolicynoC3
				, CASE WHEN yGrid = '4' THEN [Policy] END AS PolicynoC4
				, CASE WHEN yGrid = '5' THEN [Policy] END AS PolicynoC5
			FROM myTableB99
		   WHERE xGrid = 'C') AS C
	ON A.requestID = C.RequestID
  JOIN   (
		  SELECT  RequestID, [Platform] AS [Platform D]
				, CASE WHEN yGrid = '1' THEN [Policy] END AS PolicynoD1
				, CASE WHEN yGrid = '2' THEN [Policy] END AS PolicynoD2
				, CASE WHEN yGrid = '3' THEN [Policy] END AS PolicynoD3
				, CASE WHEN yGrid = '4' THEN [Policy] END AS PolicynoD4
				, CASE WHEN yGrid = '5' THEN [Policy] END AS PolicynoD5
			FROM myTableB99
		   WHERE xGrid = 'D') AS D
	ON A.requestID = D.RequestID
	GROUP BY A.RequestID, [Platform A], [Platform B], [Platform C], [Platform D]
GO


SELECT * FROM myTableA99
GO

DROP VIEW myTableA99
DROP TABLE myTableB99, myTableA99_bkp
GO

quote:

---

Originally posted by revdnrdy
Why would you want to use a view on a production database? This could create a performance hit due to IO usage. At my company we only use views for querying against a reporting server..

---

quote:

---

The original post seems to suggest that you want to remap data from A to B and then use B in production. Couldn't this be accomplished by simply copying data from A to B? I can think of a couple of ways to do this..

---

quote:

---

...10 yr old child

---