XML Generation using SQL Query and Formating

Source Table

                           Result : XML Generation using Sql query and Formatting

There are some key words to generate xml at end of the query, one of them in 'for xml auto '

Format Tips using 'for xml auto'

1.This will take table name as node name.
2.Create nodes as much table you joined in the query.
3.Columns of the table you mention in query will come as attribute.

simple example query is below for above out put




      Select 
Students.[Student] as Name
,Exams.[Exams] as Exam
,Scores.Subject as Subject
,Scores.Marks as Marks
  from 
StudentDetails as Students
join 
StudentDetails as Exams
      on
Exams.Id = Students.Id
join 
StudentDetails as Scores
    on
Scores.Id = Students.Id

  for xml auto 

Output:

                         

SSRS 2008 - Tablix Grouping

Lets assume our source table is like below image

Step: 1

1.Add the same dataset to your SSRS Report , Right click on the dataset folder- choose datasource etc,,

2.Then you dataset will appear like below image

Step :2

1.Add table to your report and assign your dataset to that table in the property

Step: 3

1.Assign the column field in the dataset.
2.As for this solution Add 'Mark' and 'Subject' field alone
3.Remove other empty columns

Step: 3
1.Right click of the row header of the row (not the row header of the column) and click on the 'add Group ' the 'Parent Group' below screen will appear

2.Select the 'Subject' field and click group header check box(This will add one row for group header)
3.Press ok

You can see group below as marked below

Step: 4
1.Repeat the step 3 and add group for 'Exam' and 'Name' field so you can see as below

Step: 5
1.Now if you run your report you can see the grouping like below image

Now add toggle to the group
1.click on the corner of 'Subject' group (Below Row groups) appears below the rdl page
2.Click on visibility field
3.Click on the hide radio button
4.Click on the 'Display can be toggle ' check box
5.Select Exams group in it.
6.Click ok

Step :6
1.Now repeat the step 5 for 'Exam' group
2.Select the toggle as 'Name' group
3.Remove the Normal Subject column
4.Make the header as bold and any other design you want
5.Run you report it will look like below

Select Rows horizontally or select Rows in columns - SQL 2008

Source Table

Result 

SOLUTION

--Temp Table Delcaration--

--Temp table for output(Structure as you want it)
Declare @Output table(ID int identity,Name varchar(50),Col1 varchar(50),Col2 varchar(50),Col3 varchar(50),Col4 varchar(50),Col5 varchar(50))
--Temp output table as like source table with rownumber column added
Declare @TempOutput table(ID int identity,Name varchar(50),Game varchar(20),Position int,RowNum int)
--Table that has instance of the source table to keep source table data
Declare @SourceInstance table(ID int identity,Name varchar(50),Game varchar(20),Position int)
--Table to keep unique colum
Declare @UniqColumnTable table(ID int identity,Name varchar(50))

--Temp Variable--
--Index to increment the loop
Declare @Index int
--Count to find the number of uniqe data
Declare @count int
--Current Name to filter data
Declare @CurName varchar(50)


--Get the all data from the source tabel to out instance table
Insert into @SourceInstance
Select Student,Game,FinalPosition from [StudentParticipants]

--Get the unique columns from the table to our table
Insert into @UniqColumnTable
Select distinct(Name) from @SourceInstance

--Assign out tem variable with data from teh table
Set @Index = 1
Set @count = (Select COUNT(*) from @UniqColumnTable)

--Start the while loop
While @Index <= @count
BEGIN
--Set  the current filter colum to the variable
Set @CurName = (Select Name from @UniqColumnTable where ID = @Index)
--Get the Data to the temp output use the data from instance table and use 'Curuname' in where so we
--get one set of unique datas into the temp output table
Insert into @TempOutput
Select Name,Game,Position,ROW_NUMBER() over (Order by ID)  from @SourceInstance Where Name= @CurName

--If you have fixed you columns as 5 then keep 5 here or wat else your max column size
--This condition means that you have datas in row is less than or equal to your column count
IF ((Select COUNT(*) from @TempOutput) <= 5)
BEGIN
Insert into @Output
Select @CurName
,Case
WHEN (Select ID from @TempOutput where RowNum = 1) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 1)
ELSE
NULL
END
as Col1
,Case
WHEN (Select ID from @TempOutput where RowNum = 2) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 2)
ELSE
NULL
END
as Col2
,Case
WHEN (Select ID from @TempOutput where RowNum = 3) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 3)
ELSE
NULL
END
as Col3
,Case
WHEN (Select ID from @TempOutput where RowNum = 4) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 4)
ELSE
NULL
END
as Col4
,Case
WHEN (Select ID from @TempOutput where RowNum = 5) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 5)
ELSE
NULL
END
as Col5

--Delete the temp output otherwise your data will remain and keep repeating in you final output table
Delete @TempOutput
--Increament the index
Set @Index = @Index +1
END
ELSE
--IF you data in row is more than your colum count we can bring the columns downs with same unique name
BEGIN
Insert into @Output
Select @CurName
,Case
WHEN (Select ID from @TempOutput where RowNum = 1) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 1)
ELSE
NULL
END
as Col1
,Case
WHEN (Select ID from @TempOutput where RowNum = 2) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 2)
ELSE
NULL
END
as Col2
,Case
WHEN (Select ID from @TempOutput where RowNum = 3) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 3)
ELSE
NULL
END
as Col3
,Case
WHEN (Select ID from @TempOutput where RowNum = 4) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 4)
ELSE
NULL
END
as Col4
,Case
WHEN (Select ID from @TempOutput where RowNum = 5) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 5)
ELSE
NULL
END
as Col5
--once the data up to ur column size is inserted, delete the inserted datas from the source instance tabele so we can fetch teh next row items
Delete @SourceInstance Where ID in(Select Top 5 ID from @SourceInstance where Name = @CurName)
--As before delete the tempoutput table
Delete @TempOutput
--The loppd will again go with same index value to fetech otheer value for the same unique column
END
END

-- finally display you out put
 Select * from @Output