Rows as column using Pivot but with multiple rows and Column
-
Hi All, I am using the following script to transpose a particular column row as columns, but the problem is I am able to get only one row, for example I have StudentNames, Ids, AggregateMarks, and TotalMarks. If I use the below approach and generate a column for every student then I am able to get only TotalMarks for each student, but I want to get Ids, AggregateMark also under each studentName
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from yourtable
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p 'exec sp_executesql @query;
Can anybody please help me in this, any code snippet or suggestion or a link would be very very helpful, please thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I am using the following script to transpose a particular column row as columns, but the problem is I am able to get only one row, for example I have StudentNames, Ids, AggregateMarks, and TotalMarks. If I use the below approach and generate a column for every student then I am able to get only TotalMarks for each student, but I want to get Ids, AggregateMark also under each studentName
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from yourtable
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p 'exec sp_executesql @query;
Can anybody please help me in this, any code snippet or suggestion or a link would be very very helpful, please thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
You could have a look at this[^]
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Hi All, I am using the following script to transpose a particular column row as columns, but the problem is I am able to get only one row, for example I have StudentNames, Ids, AggregateMarks, and TotalMarks. If I use the below approach and generate a column for every student then I am able to get only TotalMarks for each student, but I want to get Ids, AggregateMark also under each studentName
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from yourtable
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p 'exec sp_executesql @query;
Can anybody please help me in this, any code snippet or suggestion or a link would be very very helpful, please thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
I have Printed the dynamic query, it is as below. But same thing how to get that ServiceLevel from the below query, it is printing only Service id for each column in a row, but I want Service Level also in another row, can't we do it? Any sort of help would be very very helpful, please help me I am also trying my best. Thanks in advance.
SELECT [Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],[Knee Care],
[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair] from
(
select ServiceLevel, ServiceId, Name
from [Service]
) x
pivot
(
max(ServiceId)
for Name in ([Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],
[Knee Care],[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair])
)
pThanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I am using the following script to transpose a particular column row as columns, but the problem is I am able to get only one row, for example I have StudentNames, Ids, AggregateMarks, and TotalMarks. If I use the below approach and generate a column for every student then I am able to get only TotalMarks for each student, but I want to get Ids, AggregateMark also under each studentName
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from yourtable
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p 'exec sp_executesql @query;
Can anybody please help me in this, any code snippet or suggestion or a link would be very very helpful, please thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
@indian1433, here is a way (The first version is static)
DECLARE @T TABLE(StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO @T VALUES
('NAME1',1,300,503),
('NAME2',2,250,500),
('NAME3',3,378,504),
('NAME4',4,490,500),
('NAME5',5,399,500)SELECT X.ID,X.TotalMarks,t1.[AggregateMarks],X.[NAME1],X.[NAME2],X.[NAME3],X.[NAME4],X.[NAME5]
FROM @T
PIVOT
(
MAX(AggregateMarks)
FOR StudentNames
IN ([NAME1],[NAME2],[NAME3],[NAME4],[NAME5])
) AS X
JOIN @T t1 on t1.Id = X.Id
ORDER BY 1I have done this one to show you how it must go like. The dynamic version follows
CREATE TABLE #T (StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO #T VALUES
('NAME1',1,300,503),
('NAME2',2,250,500),
('NAME3',3,378,504),
('NAME4',4,490,500),
('NAME5',5,399,500)DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from #T
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')set @query = N'SELECT X.ID,X.TotalMarks,t1.AggregateMarks, ' + @cols + N' from #T
PIVOT
(
MAX(AggregateMarks)
FOR StudentNames IN (' + @cols + N')
) AS X
JOIN #T t1 on t1.Id = X.Id
ORDER BY 1'print @query
exec sp_executesql @query
DROP TABLE #T
Hope this helps.
-
Hi All, I am using the following script to transpose a particular column row as columns, but the problem is I am able to get only one row, for example I have StudentNames, Ids, AggregateMarks, and TotalMarks. If I use the below approach and generate a column for every student then I am able to get only TotalMarks for each student, but I want to get Ids, AggregateMark also under each studentName
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from yourtable
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p 'exec sp_executesql @query;
Can anybody please help me in this, any code snippet or suggestion or a link would be very very helpful, please thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
You need to "group by" your query with student name Refer related post: Pivot with dynamic columns
My Tech Blog : IT Developer Zone
-
You need to "group by" your query with student name Refer related post: Pivot with dynamic columns
My Tech Blog : IT Developer Zone
Linking to an article on your own blog, without posting the relevant details here, is considered spam.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Linking to an article on your own blog, without posting the relevant details here, is considered spam.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Okies... Thanks for the information...Will take care...
My Tech Blog : IT Developer Zone
