February 22, 2011 at 12:58 am
declare @t table
(
name nvarchar(10),
English int,
Social int
)
insert into @t
select 'John' , 100, 90
union
select 'Mary' , 35, 87
select * from @t
--Output Needed
name value
john_english 100
john_social 90
mary_english 35
mary_social 87
In Name column concatenation of text and column name is required
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 22, 2011 at 1:29 am
select name + '_english', English as Value from @t
union all
select name + '_social', Social as Value from @t
February 22, 2011 at 2:33 am
Thanks Steveb but it means if i have 100 columns i need to do 100 union.That would not be a nice approach
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 22, 2011 at 3:03 am
Bhuvnesh (2/22/2011)
Thanks Steveb but it means if i have 100 columns i need to do 100 union.That would not be a nice approach
The you would need to normalise your design. I'm not sure what the 'English' and 'Social' columns refer to in your design, however I would suggest that you split them out into another table (if your project allows this). Then you could try the approach below. Obviously you can add foreign key constraints and indexes, rename tables and columns as you wish, however it is just to illustrate the point.
DECLARE @t table(
name nvarchar(10)
typeid int
score int)
DECLARE @type table(
typeid int
typedescription nvarchar(10)
)
INSERT INTO @type
values(1, 'English');
INSERT INTO @type
values(2, 'Social');
INSERT INTO @t
values('John',1,100)
INSERT INTO @t
values('John',2,90)
INSERT INTO @t
values('Mary',1,35)
INSERT INTO @t
values('Mary',2,87)
SELECT
t.name + '_' + ty.typedescription, score
FROM @t t INNER JOIN @type ty
ON t.typeid = ty.typeid
February 22, 2011 at 3:51 am
Use below T-Sql help you to get the result . If you have more than 10 column use dynamic sql
SELECT name + '_' + column_name as [Name],
[Value]
FROM (SELECT [name],
CONVERT(VARCHAR(50), RTRIM(English )) AS English,
CONVERT(VARCHAR(50), Social) AS Social
FROM @t) MyTable UNPIVOT ([Value] FOR column_name IN (English, Social))AS MyUnPivot
February 23, 2011 at 12:10 am
Thanks Srikant . i saw your reply today only but i had the same approach yesterday to fulfill the objective.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 23, 2011 at 5:57 am
Another requirement
declare @t table
(
name nvarchar(10),
English int,
Social int
)
insert into @t
select 'John' , 100, 90
union
select 'Mary' , 35, 87
select * from @t
--Output Needed
NOw there column will be 4 columns as below
john_english,john_social,mary_english ,mary_social
100 90 35 87
it means that concatenation of name column's value with column's name to have new columns name
No if new columns = existing column * values in name column ( here 2*2 = 4)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 23, 2011 at 4:34 pm
Have a look at the CrossTab and DynamicCrossTab articles referenced in my signature.
as a side note: Pivoting and the like usually should be done at the presentation layer, not at the database layer....
February 24, 2011 at 12:08 am
i got solution for this but this is not nice one 🙁
select * from
(
select
english as [john_english],
social as [john_social]
from @t where name = 'john') a
,
(select english as [Mary_english],
social as [Mary_social]
from @t where name = 'Mary') b
So "name" column has definite and constant values.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 24, 2011 at 2:11 am
You can use below sql and its completely upto you ...
drop table ##Z
SELECT name + '_' + column_name as [Name],
[Value]
into ##Z
FROM (SELECT [name],
CONVERT(VARCHAR(50), RTRIM(English )) AS English,
CONVERT(VARCHAR(50), Social) AS Social
FROM @t) MyTable UNPIVOT ([Value] FOR column_name IN (English, Social))AS MyUnPivot
declare @Column_name VARCHAR(8000),
@cmd VARCHAR(8000)
set @Column_name=''
set @cmd=''
SELECT @Column_name = @Column_name + '[' + name + ']' + ','
FROM ##Z
SET @Column_name=left(@Column_name, len(@Column_name) - 1)
set @cmd ='SELECT *
FROM ##Z ' + 'PIVOT
(
max(value)
FOR [name] IN (' + @Column_name + ')
)
AS p'
exec( @cmd)
February 24, 2011 at 3:14 pm
Bhuvnesh (2/24/2011)
i got solution for this but this is not nice one 🙁
select * from(
select
english as [john_english],
social as [john_social]
from @t where name = 'john') a
,
(select english as [Mary_english],
social as [Mary_social]
from @t where name = 'Mary') b
So "name" column has definite and constant values.
Did you read the CrossTab article I mentioned?
Based on that article you could decide if you'd nee to move on to the DynamicCrossTab concept or not.
February 24, 2011 at 10:35 pm
Thanks LutzM , i have read that articles and got the solution .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply