Pivot data with new column idenfiying

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

General SQL Server Forums

New to SQL Server Programming

Pivot data with new column idenfiying

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-06-05 : 03:34:39

I need to write an sp as below

Existing Data

Emp_Id Emp_Name Emp_Age
1001 Ravi 25
1002 Gopal 27
1003 Kumar 30
1004 Suresh 24

New Data Resultset

Column_Name Column_Value Is_New
Emp_Id 1001 1
Emp_Name Ravi 0
Emp_Age 25 0
Emp_Id 1002 1
Emp_Name Gopal 0
Emp_Age 27 0
Emp_Id 1003 1
Emp_Name Kumar 0
Emp_Age 30 0
Emp_Id 1004 1
Emp_Name Suresh 0
Emp_Age 24 0

Is new column has to be 1 when an new row is taken for inserting

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 03:38:45

[code]
SELECT *
FROM table
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u
[/code]
I didnt understand the explanation for Is_New
what does below mean?
Is new column has to be 1 when an new row is taken for inserting
inserting where?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 03:43:28

SELECT *, CASE WHEN Column_Name = 'Emp_id' THEN 1 ELSE 0 END AS Is_New
FROM table
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 03:49:20

quote:
Originally posted by bandi

SELECT *, CASE WHEN Column_Name = 'Emp_id' THEN 1 ELSE 0 END AS Is_New
FROM table
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u

--
Chandu

Though it matches the output the explanation given is confusing
Thats why i refrained from posting it as I want to understand what exactly OP is looking for
Hopefully OP will come back with an explanation rather than being contended with the above solution which may not be exactly the intended one!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-06-05 : 04:55:05

Is new column has to be 1 when an new row is taken for display in the query window.
I am not able to get result for the
SELECT *, CASE WHEN Column_Name = 'Emp_id' THEN 1 ELSE 0 END AS Is_New
FROM table
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u

--
Chandu

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-06-05 : 04:55:42

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

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 04:56:15

quote:
Originally posted by shanmugaraj

Is new column has to be 1 when an new row is taken for display in the query window.
I am not able to get result for the
SELECT *, CASE WHEN Column_Name = 'Emp_id' THEN 1 ELSE 0 END AS Is_New
FROM table
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u

--
Chandu

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Please elaborate on what you mean by you're getting intended result with some sample data and output
Otherwise we cant make out what you're doing wrong

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 04:58:03

quote:
Originally posted by shanmugaraj

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

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

ok...that makes sense

here you go


SELECT *, CASE WHEN Column_Name = 'Emp_id' THEN 1 ELSE 0 END AS Is_New
FROM (SELECT CAST([Emp_Id] AS varchar(10)) AS Emp_Id,
[Emp_Name],
CAST([Emp_Age] AS varchar(3)) AS Emp_Age
FROM table)t
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 05:03:33

Hi Shanmugaraj,
the columns in the UNPIVOT should have same data type...
@Visakh, age should also have length of 10

quote:
Originally posted by visakh16

quote:
Originally posted by shanmugaraj

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

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

ok...that makes sense

here you go

SELECT *, CASE WHEN Column_Name = 'Emp_id' THEN 1 ELSE 0 END AS Is_New
FROM (SELECT CAST([Emp_Id] AS varchar(10)) AS Emp_Id,
[Emp_Name],
CAST([Emp_Age] AS varchar(10)) AS Emp_Age
FROM table)t
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 05:10:01

quote:
Originally posted by bandi

Hi Shanmugaraj,
the columns in the UNPIVOT should have same data type...
@Visakh, age should also have length of 10

--
Chandu

Yep true
it was an oversight

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-06-05 : 06:03:33

SELECT *, CASE WHEN Column_Name = 'Emp_id' THEN 1 ELSE 0 END AS Is_New
FROM (SELECT CAST([Emp_Id] AS varchar(10)) AS Emp_Id,
[Emp_Name],
CAST([Emp_Age] AS varchar(3)) AS Emp_Age
FROM table)t
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u

This is working fine ... thanks visakh

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 06:05:52

quote:
Originally posted by shanmugaraj

SELECT *, CASE WHEN Column_Name = 'Emp_id' THEN 1 ELSE 0 END AS Is_New
FROM (SELECT CAST([Emp_Id] AS varchar(10)) AS Emp_Id,
[Emp_Name],
CAST([Emp_Age] AS varchar(3)) AS Emp_Age
FROM table)t
UNPIVOT (Column_Value FOR Column_Name IN ([Emp_Id],[Emp_Name],[Emp_Age]))u

This is working fine ... thanks visakh

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Welcome
you still didnt answer the clarification on Is_New column logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 06:07:24

How come the above will work Shanmugaraj...
See my earlier post (Posted - 06/05/2013 : 05:03:33)

Emp_Age column length should also be same length of other two columns

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources