Displays Rows into column type
-
Helo to all Members of SqlServerCentral.. I cam here first time with a My T-sql problem..
I have a Sql server 2005 query
declare @DueDate as datetime,@Branch_code as int,@class_id as int,@yearid as int
set @Branch_code=1 set @class_id=29 set @yearid=11
set @Duedate=(SELECT MAX(Due_Date) FROM Class_FeeStructure WHERE branch_id = @Branch_code AND Class_ID = @class_id)
create table #amttable(id int identity(1,1),EnrolmentId nvarchar(max),StudentId int,FeeTypeId int,Amount float,Amount1 nvarchar(max),Concession float,BillNumber int,FeeStatus nvarchar(50),Name nvarchar(max),IsBlock int)
insert into #amttable EXEC GetFeeStatus @yearid,@Branch_code,@class_id, @Duedate
select Table1.EnrolmentId,Table1.Amount ,(select FeeName from FeeTypeTable where FeeTypeID=Table1.FeeTypeID) as HeadName,Concession,(Amount-Concession) AS BillAmount from #amttable Table1 where EnrolmentId='14408'
drop table #amttable
My output is like
This is the output of a single EnrollmentId but i wanst this output in this manner
http://social.msdn.microsoft.com/Forums/getfile/160768
How to do this
-
Looks like you need a cross tab query, there are two links in my signature by Jeff on pivots and cross tabs which would help you out.
If you get stuck, please provide DDL and sample data as per the 2nd link in my signature.
-
Hii Thanks for Reply,, This is the query when i run this query i will get the output
Declare @Table Table
(
EnrolmentId Int,
Amount Int,
HeadName Varchar(30),
Concession Int,
Billamount Int
)
Insert @Table
Select 14408,4950,'Tution',0,4950 Union all
Select 14408,131,'Computer',0,1313 Union all
Select 14408,450,'smartGlass',0,450 union All
Select 14408,1314,'Development',0,1314 Union All
Select 14408,2040,'Credit',0,2040;
Select A.EnrolmentId,Tution,TConcession Concession,
Computer ,cConcession Concession,
smartGlass,SConcession Concession,
Develpoment,DConcession Concession,
Credit,CrConcession Concession
From
(
Select A.EnrolmentId,
Sum(Tution)Tution,
Sum(Computer)Computer,
Sum(smartGlass)smartGlass,
Sum(Development)Develpoment,
Sum(Credit) Credit
From
(
Select * From @Table T1
Pivot
(
Sum(Amount) For HeadName In ("Tution","Computer","smartGlass","Development","Credit")
) Pvt
) As A
Group By A.EnrolmentId
)A
Inner join
(
Select EnrolmentId,
Sum(Tution)TConcession,
Sum(Computer)CConcession,
Sum(smartGlass)SConcession,
Sum(Development)DConcession,
Sum(Credit) CrConcession
From
(
Select * From @Table T1
Pivot
(
Sum(Concession) For HeadName In ("Tution","Computer","smartGlass","Development","Credit")
) Pvt
) As A
Group By A.EnrolmentId
) T On t.EnrolmentId = A.EnrolmentId
--Select * From @Table T1
--Pivot
--(
--Sum(Concession) For HeadName In ("Tution","Computer","smartGlass","Development","Credit")
--) Pvt
But actually i have One problem, i have hardcoded
the head name
"Tution","Computer","smartGlass","Development","Credit"-
but these head names are coming from FeeType table accoding to Different class.. so how i could mange this.and this i am doing for a single Enrollment Id.. I wants to do this for all Enrollment id in StudentTable
-
Then you will need to lookup dynamic pivots and dynamic cross tab queries.
Part 2 of the links provided goes into dynamic cross tabs, and lots on dynamic pivot queries on the internet.
-
Thanks for Co-operating
I tried alot but my Output is not coming
Actually i have an Stored procedure and i am passing three parameters the one is classid second is session id and third is yearid..Corresponding to this i have fetching all students record in that particular class i.e this one
This is the record of a single class Now how will i convert it into the output i need as you know
-
please provide create table scripts for all tables involved in your query, sample data for each table and expected outcomes based on that sample data
-
Ok for that i have created a Single table..In this table there is a data of all the students of one class.. Now plz tell me that how to get desired Output
of all the Students.I am providiing you a script plz run in your database and provide me the output with in a single table..
USE [Erpdemo]
GO
/****** Object: Table [dbo].[Sample_Table1] Script Date: 09/03/2012 19:02:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Sample_Table1](
[EnrolmentId] [nvarchar](max) NULL,
[StudentId] [int] NULL,
[FeeTypeId] [int] NULL,
[FeeName] [nvarchar](150) NULL,
[Amount] [float] NULL,
[Amount1] [nvarchar](max) NULL,
[Concession] [float] NOT NULL,
[BillNumber] [int] NOT NULL,
[FeeStatus] [varchar](1) NOT NULL,
[Name] [nvarchar](max) NULL,
[IsBlock] [nvarchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14408', 4568, 2, N'Tuition', 4950, N'$4950', 0, 61885, N'$', N'AAROHI BANSAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14408', 4568, 4, N'Smartclass', 450, N'$450', 0, 61885, N'$', N'AAROHI BANSAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'13984', 4064, 2, N'Tuition', 4950, N'$4950', 0, 61886, N'$', N'AKSHAT GUPTA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'13984', 4064, 4, N'Smartclass', 450, N'$450', 0, 61886, N'$', N'AKSHAT GUPTA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14371', 4531, 2, N'Tuition', 4950, N'$4950', 0, 61887, N'$', N'ANISH SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14371', 4531, 4, N'Smartclass', 450, N'$450', 0, 61887, N'$', N'ANISH SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14260', 4422, 13, N'Pending', 600, N'#600', 0, 61888, N'#', N'ANNETTE ANN JEAN ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14260', 4422, 2, N'Tuition', 4950, N'#4950', 0, 61888, N'#', N'ANNETTE ANN JEAN ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14260', 4422, 4, N'Smartclass', 450, N'#450', 0, 61888, N'#', N'ANNETTE ANN JEAN ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14260', 4422, 11, N'Credit', 1830, N'#1830', 0, 61888, N'#', N'ANNETTE ANN JEAN ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14439', 4598, 2, N'Tuition', 4950, N'#4950', 0, 61889, N'#', N'ARNAV JAIN', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14439', 4598, 4, N'Smartclass', 450, N'#450', 0, 61889, N'#', N'ARNAV JAIN', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14250', 4410, 2, N'Tuition', 4950, N'$4950', 0, 61890, N'$', N'AZAN ALI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14250', 4410, 4, N'Smartclass', 450, N'$450', 0, 61890, N'$', N'AZAN ALI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14299', 4461, 2, N'Tuition', 4950, N'$4950', 0, 61891, N'$', N'CHAHAK KHANDELWAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14299', 4461, 4, N'Smartclass', 450, N'$450', 0, 61891, N'$', N'CHAHAK KHANDELWAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14104', 4194, 10, N'Fine', 23, N'#23', 0, 61892, N'#', N'D.LAKSHNA REDDY ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14104', 4194, 2, N'Tuition', 4950, N'#4950', 0, 61892, N'#', N'D.LAKSHNA REDDY ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14104', 4194, 4, N'Smartclass', 450, N'#450', 0, 61892, N'#', N'D.LAKSHNA REDDY ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14286', 4448, 2, N'Tuition', 4950, N'$4950', 0, 61893, N'$', N'DHANAK BHAT ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14286', 4448, 4, N'Smartclass', 450, N'$450', 0, 61893, N'$', N'DHANAK BHAT ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14286', 4448, 13, N'Pending', 600, N'$600', 0, 61893, N'$', N'DHANAK BHAT ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14251', 4411, 2, N'Tuition', 4950, N'$2475', 2475, 61894, N'$', N'DHANIKA NANGIA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14251', 4411, 4, N'Smartclass', 450, N'$450', 0, 61894, N'$', N'DHANIKA NANGIA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14217', 4370, 2, N'Tuition', 4950, N'#4950', 0, 61895, N'#', N'INSHA RANGREJ ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14217', 4370, 4, N'Smartclass', 450, N'#450', 0, 61895, N'#', N'INSHA RANGREJ ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14217', 4370, 10, N'Fine', 91, N'#91', 0, 61895, N'#', N'INSHA RANGREJ ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14217', 4370, 13, N'Pending', 5444, N'#5444', 0, 61895, N'#', N'INSHA RANGREJ ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14223', 4376, 2, N'Tuition', 4950, N'$4950', 0, 61896, N'$', N'JAIESH SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14223', 4376, 4, N'Smartclass', 450, N'$450', 0, 61896, N'$', N'JAIESH SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14384', 4544, 2, N'Tuition', 4950, N'#4950', 0, 61897, N'#', N'KAUSER QURESHI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14384', 4544, 4, N'Smartclass', 450, N'#450', 0, 61897, N'#', N'KAUSER QURESHI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14314', 4476, 2, N'Tuition', 4950, N'$4950', 0, 61898, N'$', N'KUNJAL VERMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14314', 4476, 4, N'Smartclass', 450, N'$450', 0, 61898, N'$', N'KUNJAL VERMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14254', 4414, 2, N'Tuition', 4950, N'$4950', 0, 61899, N'$', N'MILIND SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14254', 4414, 4, N'Smartclass', 450, N'$450', 0, 61899, N'$', N'MILIND SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14391', 4551, 2, N'Tuition', 4950, N'$4950', 0, 61900, N'$', N'NEERAJ SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14391', 4551, 4, N'Smartclass', 450, N'$450', 0, 61900, N'$', N'NEERAJ SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14265', 4427, 2, N'Tuition', 4950, N'$4950', 0, 61901, N'$', N'PALAK AGARWAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14265', 4427, 4, N'Smartclass', 450, N'$450', 0, 61901, N'$', N'PALAK AGARWAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14265', 4427, 13, N'Pending', 600, N'$600', 0, 61901, N'$', N'PALAK AGARWAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14216', 4369, 2, N'Tuition', 4950, N'#4950', 0, 61902, N'#', N'PINKY RANGREJ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14216', 4369, 4, N'Smartclass', 450, N'#450', 0, 61902, N'#', N'PINKY RANGREJ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14216', 4369, 10, N'Fine', 91, N'#91', 0, 61902, N'#', N'PINKY RANGREJ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14216', 4369, 13, N'Pending', 5445, N'#5445', 0, 61902, N'#', N'PINKY RANGREJ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14188', 4278, 2, N'Tuition', 4950, N'$4950', 0, 61903, N'$', N'PRIYANSHI KUMAWAT ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14188', 4278, 4, N'Smartclass', 450, N'$450', 0, 61903, N'$', N'PRIYANSHI KUMAWAT ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14164', 4254, 10, N'Fine', 16, N'$16', 0, 61904, N'$', N'ZEESHAN HUSSAIN', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14164', 4254, 2, N'Tuition', 4950, N'$4950', 0, 61904, N'$', N'ZEESHAN HUSSAIN', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14164', 4254, 4, N'Smartclass', 450, N'$450', 0, 61904, N'$', N'ZEESHAN HUSSAIN', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14408', 4568, 11, N'Credit', 2040, N'$2040', 0, 63403, N'$', N'AAROHI BANSAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14469', 4687, 2, N'Tuition', 4950, N'#0', 4950, 63417, N'#', N'TANISHA BIST', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14469', 4687, 3, N'Computer', 0, N'#0', 0, 63417, N'#', N'TANISHA BIST', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14469', 4687, 4, N'Smartclass', 450, N'#0', 450, 63417, N'#', N'TANISHA BIST', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14469', 4687, 7, N'Term', 0, N'#0', 0, 63417, N'#', N'TANISHA BIST', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14469', 4687, 8, N'Examination', 0, N'#0', 0, 63417, N'#', N'TANISHA BIST', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14469', 4687, 9, N'Development', 0, N'#0', 0, 63417, N'#', N'TANISHA BIST', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14469', 4687, 1, N'Admission', 3000, N'#0', 3000, 63417, N'#', N'TANISHA BIST', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14494', 4712, 2, N'Tuition', 4950, N'$4950', 0, 63421, N'$', N'DURVANSHI SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14494', 4712, 4, N'Smartclass', 450, N'$450', 0, 63421, N'$', N'DURVANSHI SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14494', 4712, 1, N'Admission', 3000, N'$3000', 0, 63421, N'$', N'DURVANSHI SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14485', 4703, 2, N'Tuition', 4950, N'$4950', 0, 63422, N'$', N'HARSH KUMAR SONI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14485', 4703, 4, N'Smartclass', 450, N'$450', 0, 63422, N'$', N'HARSH KUMAR SONI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14464', 4682, 2, N'Tuition', 4950, N'$4950', 0, 63423, N'$', N'JATIN BANSIRAMANI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14464', 4682, 4, N'Smartclass', 450, N'$450', 0, 63423, N'$', N'JATIN BANSIRAMANI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14464', 4682, 1, N'Admission', 3000, N'$3000', 0, 63423, N'$', N'JATIN BANSIRAMANI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14497', 4715, 2, N'Tuition', 4950, N'$4950', 0, 63424, N'$', N'KHYAT UNCHWAL', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14497', 4715, 4, N'Smartclass', 450, N'$450', 0, 63424, N'$', N'KHYAT UNCHWAL', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14497', 4715, 1, N'Admission', 3000, N'$3000', 0, 63424, N'$', N'KHYAT UNCHWAL', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14463', 4681, 2, N'Tuition', 4950, N'$2475', 2475, 63425, N'$', N'KRISHNA BADAYA', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14463', 4681, 4, N'Smartclass', 450, N'$450', 0, 63425, N'$', N'KRISHNA BADAYA', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14463', 4681, 1, N'Admission', 3000, N'$3000', 0, 63425, N'$', N'KRISHNA BADAYA', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14496', 4714, 2, N'Tuition', 4950, N'#0', 4950, 63426, N'#', N'RIDDHI MAHESHWARI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14496', 4714, 4, N'Smartclass', 450, N'#0', 450, 63426, N'#', N'RIDDHI MAHESHWARI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14496', 4714, 1, N'Admission', 3000, N'#0', 3000, 63426, N'#', N'RIDDHI MAHESHWARI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14495', 4713, 2, N'Tuition', 4950, N'#0', 4950, 63427, N'#', N'SIDDHI MAHESHWARI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14495', 4713, 4, N'Smartclass', 450, N'#0', 450, 63427, N'#', N'SIDDHI MAHESHWARI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14495', 4713, 1, N'Admission', 3000, N'#0', 3000, 63427, N'#', N'SIDDHI MAHESHWARI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14486', 4704, 2, N'Tuition', 4950, N'$4950', 0, 63428, N'$', N'VYOM THADANI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14486', 4704, 4, N'Smartclass', 450, N'$450', 0, 63428, N'$', N'VYOM THADANI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14486', 4704, 1, N'Admission', 3000, N'$3000', 0, 63428, N'$', N'VYOM THADANI ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14464', 4682, 11, N'Credit', 1215, N'$1215', 0, 63434, N'$', N'JATIN BANSIRAMANI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14485', 4703, 1, N'Admission', 3000, N'$3000', 0, 63451, N'$', N'HARSH KUMAR SONI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14494', 4712, 11, N'Credit', 615, N'#615', 0, 63457, N'#', N'DURVANSHI SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'13984', 4064, 9, N'Development', 750, N'$750', 0, 63464, N'$', N'AKSHAT GUPTA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'13984', 4064, 8, N'Examination', 150, N'$150', 0, 63464, N'$', N'AKSHAT GUPTA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'13984', 4064, 7, N'Term', 250, N'$250', 0, 63464, N'$', N'AKSHAT GUPTA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14408', 4568, 3, N'Computer', 1313, N'$1313', 0, 63494, N'$', N'AAROHI BANSAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14408', 4568, 9, N'Development', 1314, N'$1314', 0, 63494, N'$', N'AAROHI BANSAL ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14104', 4194, 13, N'Pending', 5423, N'5423', 0, 0, N'', N'D.LAKSHNA REDDY ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14494', 4712, 13, N'Pending', 615, N'615', 0, 0, N'', N'DURVANSHI SHARMA ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14216', 4369, 13, N'Pending', 10936, N'10936', 0, 0, N'', N'PINKY RANGREJ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14384', 4544, 13, N'Pending', 5400, N'5400', 0, 0, N'', N'KAUSER QURESHI', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14253', 4413, 13, N'Pending', 6340, N'6340', 0, 0, N'', N'ARASHAN AHMED CHANDER ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'14217', 4370, 13, N'Pending', 10935, N'10935', 0, 0, N'', N'INSHA RANGREJ ', NULL)
INSERT [dbo].[Sample_Table1] ([EnrolmentId], [StudentId], [FeeTypeId], [FeeName], [Amount], [Amount1], [Concession], [BillNumber], [FeeStatus], [Name], [IsBlock]) VALUES (N'13984', 4064, 14, N'Cautionmoney', 2300, N'2300', 0, 0, N'', N'AKSHAT GUPTA ', NULL)
-
and what is the required output based on this sample data
-
Thanks For Supporting
I wants that the Output should come like
http://social.msdn.microsoft.com/Forums/getfile/160768
This is the output for a single Student(EnrolmentId)..I need the same Output for all the Student (EnrolmentId) in this table..
and also i need this all in Single Result Set.
-
your expected outcome is not based on the sample data you have provided
SELECT
EnrolmentId,
StudentId,
Name,
MAX(CASE WHEN FeeTypeID = 1 THEN Amount END) AS AdmissionAmount,
MAX(CASE WHEN FeeTypeID = 2 THEN Amount END) AS TuitionAmount,
MAX(CASE WHEN FeeTypeID = 3 THEN Amount END) AS ComputerAmount,
MAX(CASE WHEN FeeTypeID = 4 THEN Amount END) AS SmartclassAmount,
MAX(CASE WHEN FeeTypeID = 7 THEN Amount END) AS TermAmount,
MAX(CASE WHEN FeeTypeID = 8 THEN Amount END) AS ExaminationAmount,
MAX(CASE WHEN FeeTypeID = 9 THEN Amount END) AS DevelopmentAmount,
MAX(CASE WHEN FeeTypeID = 10 THEN Amount END) AS FineAmount,
MAX(CASE WHEN FeeTypeID = 11 THEN Amount END) AS CreditAmount,
MAX(CASE WHEN FeeTypeID = 13 THEN Amount END) AS PendingAmount,
MAX(CASE WHEN FeeTypeID = 14 THEN Amount END) AS CautionMoneyAmount
FROM
Sample_Table1
GROUP BY
EnrolmentId,
StudentId,
Name
-
Thats Wonderful...First time i came here and i got Result..Good Work..
I don't know how to mark as answer..
-
You cannot mark your topics as answered on this site.
The solution I gave is a simple cross tab query which is detailed in the links I referenced by Jeff from my signature.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply