How to change row to column?
select Name,
sum(CASE
when [subject]='physics' then Mark
end) as Physics,
sum(CASE
when [subject]='chemistry' then Mark
end) as chemistry,
sum(CASE
when [subject]='maths' then Mark
end) as maths
from t group by Name
Or if you need it in one line:
SELECT
t1.name,
MemberList = substring((SELECT ( ', ' + subject+' - '+
cast(Mark as varchar(100)) )
FROM t t2
WHERE t1.name = t2.name
ORDER BY
name,
subject
FOR XML PATH( '' )
), 3, 1000 )FROM t t1
GROUP BY name
source to share
You need to use SQL Pivoting, check the examples in SQL SERVER - PIVOT and UNPIVOT Table Examples . Using Sql Pivoting you can change rows to columns and Unpivoting is converting columns to rows.
Please note, I am checking if I can provide you with the exact script, but so far the link will not help you.
UPDATE
Sample Code
Although I haven't tested this with actual data, but it parses great.
-- Pivot Table ordered by Name of Student
SELECT Name, Physics, Chemistry, Maths
FROM (
SELECT Name, Subject, Mark
FROM Student) up
PIVOT (SUM(Mark) FOR Student IN (Physics, Chemistry, Maths)) AS pvt
ORDER BY Name
-- Result should be something like
----------------------------------
Name Physics Chemistry Maths
----------------------------------
Aswin 100 300 200
----------------------------------
To create a bar, you need to know the actual row values to convert to columns. I already wrote about dynamic pivoting here if you find it helpful.
source to share
It is not entirely clear if you want this data in separate columns or in a single column.
If you want it to be in separate columns, you can use a feature PIVOT
that is now available in SQL Server 2005.
If you know all the values you want to convert or have a limited number, you can program the query:
select *
from
(
select name, subject +' '+ cast(mark as varchar(9)) as sub_mark,
'Subject_'+cast(row_number() over(partition by name
order by subject) as varchar(10)) col_name
from subjects
) s
pivot
(
max(sub_mark)
for col_name in (Subject_1, Subject_2, Subject_3)
) piv;
See SQL Fiddle with Demo . You will notice that I did it a little differently than the other answer. I have placed both objects / labels in the same column with the column name Subject_1
etc.
If you have an unknown number of values, you can use dynamic sql:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Subject_'+cast(row_number() over(partition by name
order by subject) as varchar(10)))
from subjects
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT name,' + @cols + ' from
(
select name, subject +'' ''+ cast(mark as varchar(9)) as sub_mark,
''Subject_''+cast(row_number() over(partition by name
order by subject) as varchar(10)) col_name
from subjects
) x
pivot
(
max(sub_mark)
for col_name in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo . The dynamic sql version will grow in the number of columns if it name
has more than 3 items.
The result of both queries is:
| NAME | SUBJECT_1 | SUBJECT_2 | SUBJECT_3 |
---------------------------------------------------
| Aswin | chemistry 300 | maths 200 | physics 100 |
source to share