Updating table for distinct values

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 2005 Forums

Transact-SQL (2005)

Updating table for distinct values

Author

Topic

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 02:02:41

The base table is as follows. Except Name column, all other columns are of decimal type.

Name Red White Black Cyan Orange
Jayme 21 0 34 9 34
Abr 94 0 0 1 2
Cnt 87 87 54 87 9

My requirement is if the value is repeated in a row, need to add 0.1 to that data. we can Ignore zero's(no need to add 0.1 to zeros). See the output below.

Need output as below.

Name Red White Black Cyan Orange
Jayme 21 0 34 9 34.01
Abr 94 0 0 1 2
Cnt 87 87.01 54 87.02 9

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 02:22:18

[code]DECLARE @Sample TABLE
(
Name VARCHAR(20),
Red MONEY,
White MONEY,
Black MONEY,
Cyan MONEY,
Orange MONEY
)

INSERT @Sample
SELECT 'Jayme', 21, 0, 34, 9, 34 UNION ALL
SELECT 'Abr', 94, 0, 0, 1, 2 UNION ALL
SELECT 'Cnt', 87, 87, 54, 87, 9

SELECT *
FROM @Sample
ORDER BY Name

;WITH Yak (Name, theCol, theValue)
AS (
SELECT u.Name,
u.theCol,
u.theValue + 0.1E * (ROW_NUMBER() OVER (PARTITION BY u.Name, u.theValue ORDER BY CASE u.theCol WHEN 'Red' THEN 1 WHEN 'White' THEN 2 WHEN 'Black' THEN 3 WHEN 'Cyan' THEN 4 WHEN 'Orange' THEN 5 END) - 1)
FROM @Sample AS s
UNPIVOT (
theValue
FOR theCol IN (s.Red, s.White, s.Black, s.Cyan, s.Orange)
) AS u
)

SELECT p.Name,
p.Red,
p.White,
p.Black,
p.Cyan,
p.Orange
FROM Yak AS y
PIVOT (
MAX(y.theValue)
FOR y.theCol IN ([Red], [White], [Black], [Cyan], [Orange])
) AS p
ORDER BY p.Name[/code]

E 12°55'05.63"
N 56°04'39.26"

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 02:38:27

I am getting error when i execute the below CTE

WITH Yak (Name, theCol, theValue)
AS (
SELECT u.Name,
u.theCol,
u.theValue + 0.1E * (ROW_NUMBER() OVER (PARTITION BY u.Name, u.theValue ORDER BY CASE u.theCol WHEN 'Red' THEN 1 WHEN 'White' THEN 2 WHEN 'Black' THEN 3 WHEN 'Cyan' THEN 4 WHEN 'Orange' THEN 5 END) - 1)
FROM Tbl_Color AS s
UNPIVOT (
theValue
FOR theCol IN (s.Red, s.White, s.Black, s.Cyan, s.Orange)
) AS u
)

Showing Error in Last line
Getting Error as
Msg 102, Level 15, State 1, Line 11

When i try to execute only this part, i am getting this error
SELECT u.Name,
u.theCol,
u.theValue + 0.1E * (ROW_NUMBER() OVER (PARTITION BY u.Name, u.theValue ORDER BY CASE u.theCol WHEN 'Red' THEN 1 WHEN 'White' THEN 2 WHEN 'Black' THEN 3 WHEN 'Cyan' THEN 4 WHEN 'Orange' THEN 5 END) - 1)
FROM Tbl_Color AS s
UNPIVOT (
theValue
FOR theCol IN (s.Red, s.White, s.Black, s.Cyan, s.Orange)
) AS u

Msg 8167, Level 16, State 1, Line 1
The type of column "Orange" conflicts with the type of other columns specified in the UNPIVOT list.
Incorrect syntax near ')'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 02:56:08

A CTE must always be followed by a referencing statement otherwise it will generate error(s).
Run both the CTE declaration AND the following statement in one batch.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 03:00:59

And how about the multiple zero values for Abr?
Should they also increment by 0.1, because we don't know due to missing in expected output.

This is my output from above complete sample code

Name	Red	White	Black	Cyan	Orange
Abr	94.00	 0.00	 0.00	 1.00	 2.00
Cnt	87.00	87.00	54.00	87.00	 9.00
Jayme	21.00	 0.00	34.00	 9.00	34.00

Name	Red	White	Black	Cyan	Orange
Abr	94.00	 0.00	 0.10	 1.00	 2.00
Cnt	87.00	87.10	54.00	87.20	 9.00
Jayme	21.00	 0.00	34.00	 9.00	34.10

E 12°55'05.63"
N 56°04'39.26"

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 03:04:52

No, we need to ignore the zero's.

quote:
Originally posted by Peso

And how about the multiple zero values for Abr?
Should they also increment by 0.1, because we don't know due to missing in expected output.

This is my output from above complete sample code
Name Red White Black Cyan Orange
Abr 94.00 0.00 0.00 1.00 2.00
Cnt 87.00 87.00 54.00 87.00 9.00
Jayme 21.00 0.00 34.00 9.00 34.00

Name Red White Black Cyan Orange
Abr 94.00 0.00 0.10 1.00 2.00
Cnt 87.00 87.10 54.00 87.20 9.00
Jayme 21.00 0.00 34.00 9.00 34.10

E 12°55'05.63"
N 56°04'39.26"

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 03:06:50

Pso, i am getting following error.
Msg 8167, Level 16, State 1, Line 5
The type of column "Orange" conflicts with the type of other columns specified in the UNPIVOT list.

I am new to Common table expressions, not knowing..where it is getting error. I am executing the following thing but getting above error

SELECT *
FROM Tbl_Color
ORDER BY Name;

WITH Yak (Name, theCol, theValue)
AS (
SELECT u.Name,
u.theCol,
u.theValue + 0.1E * (ROW_NUMBER() OVER (PARTITION BY u.Name, u.theValue ORDER BY CASE u.theCol WHEN 'Red' THEN 1 WHEN 'White' THEN 2 WHEN 'Black' THEN 3 WHEN 'Cyan' THEN 4 WHEN 'Orange' THEN 5 END) - 1)
FROM Tbl_Color AS s
UNPIVOT (
theValue
FOR theCol IN (s.Red, s.White, s.Black, s.Cyan, s.Orange)
) AS u
)

SELECT p.Name,
p.Red,
p.White,
p.Black,
p.Cyan,
p.Orange
FROM Yak AS y
PIVOT (
MAX(y.theValue)
FOR y.theCol IN ([Red], [White], [Black], [Cyan], [Orange])
) AS p
ORDER BY p.Name

quote:
Originally posted by Peso

A CTE must always be followed by a referencing statement otherwise it will generate error(s).
Run both the CTE declaration AND the following statement in one batch.

E 12°55'05.63"
N 56°04'39.26"

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 03:15:56

I need to update the table with above requirment.. instead of retriving data.

quote:
Originally posted by Peso

A CTE must always be followed by a referencing statement otherwise it will generate error(s).
Run both the CTE declaration AND the following statement in one batch.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 03:18:10

Why are you editing parts of the code? And then complains it doesn't work?
Do me a favor and test the complete original sample code suggested by me.
Does that work for you?

When you have confirmed that, THEN you can move on for adapting to your environment...

And as of the zero values, it would be REALLY nice if that business rule have been mentioned in the original post. Or you do enjoy having us to work twice as hard?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 03:20:08

This is the code altered for handling with the zero values.
Your error message above is derived from you NOT HAVING same datatypes for the color columns...

DECLARE	@Sample TABLE
	(
		Name VARCHAR(20),
		Red MONEY,
		White MONEY,
		Black MONEY,
		Cyan MONEY,
		Orange MONEY
	)

INSERT	@Sample
SELECT	'Jayme', 21, 0, 34, 9, 34 UNION ALL
SELECT	'Abr', 94, 0, 0, 1, 2 UNION ALL
SELECT	'Cnt', 87, 87, 54, 87, 9

SELECT		*
FROM		@Sample
ORDER BY	Name

;WITH Yak (Name, theCol, theValue)
AS (
	SELECT	u.Name,
		u.theCol,
		CASE u.theValue
			WHEN 0 THEN 0
			ELSE 0.1E * (ROW_NUMBER() OVER (PARTITION BY u.Name, u.theValue ORDER BY CASE u.theCol WHEN 'Red' THEN 1 WHEN 'White' THEN 2 WHEN 'Black' THEN 3 WHEN 'Cyan' THEN 4 WHEN 'Orange' THEN 5 END) - 1)
		END + u.theValue
	FROM	@Sample AS s
	UNPIVOT	(
			theValue
			FOR theCol IN (s.Red, s.White, s.Black, s.Cyan, s.Orange)
		) AS u
)

SELECT		p.Name,
		p.Red,
		p.White,
		p.Black,
		p.Cyan,
		p.Orange
FROM		Yak AS y
PIVOT		(
			MAX(y.theValue)
			FOR y.theCol IN ([Red], [White], [Black], [Cyan], [Orange])
		) AS p
ORDER BY	p.Name

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 03:29:51

And this is how you turn the last SELECT into an update of source table.

DECLARE	@Sample TABLE
	(
		Name VARCHAR(20),
		Red MONEY,
		White MONEY,
		Black MONEY,
		Cyan MONEY,
		Orange MONEY
	)

INSERT	@Sample
SELECT	'Jayme', 21, 0, 34, 9, 34 UNION ALL
SELECT	'Abr', 94, 0, 0, 1, 2 UNION ALL
SELECT	'Cnt', 87, 87, 54, 87, 9

SELECT		*
FROM		@Sample
ORDER BY	Name

;WITH Yak (Name, theCol, theValue)
AS (
	SELECT	u.Name,
		u.theCol,
		CASE u.theValue
			WHEN 0 THEN 0
			ELSE 0.1E * (ROW_NUMBER() OVER (PARTITION BY u.Name, u.theValue ORDER BY CASE u.theCol WHEN 'Red' THEN 1 WHEN 'White' THEN 2 WHEN 'Black' THEN 3 WHEN 'Cyan' THEN 4 WHEN 'Orange' THEN 5 END) - 1)
		END + u.theValue
	FROM	@Sample AS s
	UNPIVOT	(
			theValue
			FOR theCol IN (s.Red, s.White, s.Black, s.Cyan, s.Orange)
		) AS u
)

UPDATE		s
SET		s.Red = d.Red,
		s.White = d.White,
		s.Black = d.Black,
		s.Cyan = d.Cyan,
		s.Orange = d.Orange
FROM		@Sample AS s
INNER JOIN	(
			SELECT		p.Name,
					p.Red,
					p.White,
					p.Black,
					p.Cyan,
					p.Orange
			FROM		Yak AS y
			PIVOT		(
						MAX(y.theValue)
						FOR y.theCol IN ([Red], [White], [Black], [Cyan], [Orange])
					) AS p
		) AS d ON d.Name = s.Name

SELECT		*
FROM		@Sample
ORDER BY	Name

E 12°55'05.63"
N 56°04'39.26"

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 03:33:57

Peso, First of all my apologizes. I didn;t see entire query from your post. I just thought by looking the following thing that you are creating some sample data to runt the query. Now i realized that these part also a part of the query.

DECLARE @Sample TABLE
(
Name VARCHAR(20),
Red MONEY,
White MONEY,
Black MONEY,
Cyan MONEY,
Orange MONEY
)

INSERT @Sample
SELECT 'Jayme', 21, 0, 34, 9, 34 UNION ALL
SELECT 'Abr', 94, 0, 0, 1, 2 UNION ALL
SELECT 'Cnt', 87, 87, 54, 87, 9

Your sample data and your query is working fine. But my problem is i already have a data in a table. i just want to update this for business requirement. The table contains lakhs of recrods. I just gave only 3 records as input for testing. Please let me know how can i proceed here?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 03:48:03

this is the actual Query. Just change the part in RED to your table name

;WITH Yak (Name, theCol, theValue)
AS (
	SELECT	u.Name,
		u.theCol,
		CASE u.theValue
			WHEN 0 THEN 0
			ELSE 0.1E * (ROW_NUMBER() OVER (PARTITION BY u.Name, u.theValue ORDER BY CASE u.theCol WHEN 'Red' THEN 1 WHEN 'White' THEN 2 WHEN 'Black' THEN 3 WHEN 'Cyan' THEN 4 WHEN 'Orange' THEN 5 END) - 1)
		END + u.theValue
	FROM	@Sample AS s
	UNPIVOT	(
			theValue
			FOR theCol IN (s.Red, s.White, s.Black, s.Cyan, s.Orange)
		) AS u
)

UPDATE		s
SET		s.Red = d.Red,
		s.White = d.White,
		s.Black = d.Black,
		s.Cyan = d.Cyan,
		s.Orange = d.Orange
FROM		@Sample AS s
INNER JOIN	(
			SELECT		p.Name,
					p.Red,
					p.White,
					p.Black,
					p.Cyan,
					p.Orange
			FROM		Yak AS y
			PIVOT		(
						MAX(y.theValue)
						FOR y.theCol IN ([Red], [White], [Black], [Cyan], [Orange])
					) AS p
		) AS d ON d.Name = s.Name

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 03:51:22

And change datatype of Orange column to same datatype as the other columns.

E 12°55'05.63"
N 56°04'39.26"

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 03:53:05

I did the Same. Just i changed the table name. But i am getting below error.

Msg 8167, Level 16, State 1, Line 1
The type of column "Orange" conflicts with the type of other columns specified in the UNPIVOT list.

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 03:55:58

thank you guys. Finally i got the result. Sorry for making some confusion in middle. NOw my last queestion. As i mention already in my last post. Actually i want to update the data in the tabel with above requirement. How can we chaange this statement to update query?