How to convert rows into columns in sql server 2008 R2?

In specific case we have to convert rows into column in sql server. Here we use pivot function to convert rows into column.

Create table ProductDetails(Cust varchar(50),Product varchar(30),Qty int)

Insert into ProductDetails(Cust,Product,Qty)

Values('MacD','Burger',10)

Insert into ProductDetails(Cust,Product,Qty)

Values('MacD','Coke',12)

Insert into ProductDetails(Cust,Product,Qty)

Values('KFC','Chicken',10)

Insert into ProductDetails(Cust,Product,Qty)

Values('KFC','Burger',10)

Insert into ProductDetails(Cust,Product,Qty)

Values('Dominos','Burger',10)

Insert into ProductDetails(Cust,Product,Qty)

Values('Dominos','Pizza',10)

Select Product,KFC,MacD,Dominos

from

(Select Product,Cust,Qty from ProductDetails) P

pivot(Sum(Qty) for Cust in(KFC,MacD,Dominos)) as pvt

 order by product

OutPut:-

SSRS interview questions answer?

What is SSRS in asp.net.

Sql Server Reporting Service is a server-based report generation software system from Microsoft. Reporting Service includes a complete set of tools for you to create ,manage, and deliver reports and API  that enables developers to integrate  or extend data and report processing in custom  application.

With reporting you can create interactive ,tabular, graphical ,or free from report s from relational , multidimensional , or xml-based data sources.Reports  can include rich  data visualization , including charts, maps, and sparklines. You can publish reports, schedule report processing , or access report on demand. You can select from a variety of viewing formats , export reports to other applications such as Microsoft excel , and  subscribe to published report . The report that you create can be viewed over a web based connection or as part of a Microsoft  windows application or sharepoint sites.

What are reporting service components in SSRS?

·                Report Designer  : -  a place where report is designed or created.

·                Report Server :- provides services for implementation and delivery of reports

·                Report Manager :- a web based administration tool to manage the report server

What is dataset in report?

DataSet is a set of data which we want to show in report. Data source is the source of data from where we are getting the data (databasename,database sever name , connection string).

SSRS report supporting other database except Microsoft Sql Sever.

SSRS is supporting other database which is based on relational or multidimensional data source like oracle, OLEDB,ODBC etc.

What are  different types of data source in SSRS?

·                Microsoft Sql Server

·                Oracle

·                OLEDB

·                ODBC

·                 SQL Server Analysis Service

·                Report Server Model

·                SAP Net Weaver  BI

·               Hyperion

·               Teradata

·                XMl

What are the types of SSRS?

·                   Parameterized Reports

·                  Linked Reports

·                 Snapshot reports

·                 Cached Reports

·                  Ad hoc Reports

·                Clickthrough Reports

·                 DrillDown Reports

·                Drillthrough Reports

·                 SubReports

What are the advantages of SSRS or why we should use SSRS?

Sql Server Reporting Service has some advantages …

·         Easy to deploy :- SSRS are stored on a centralized web server. Because reports are centralized , users can run report from one place.

·         Easy export for further analysis with different file format :-  User can export the reports in different file format like word, excel , pdf, csv , xml and image etc. Reports are editable if you are importing in excel or word format.

·          Ability to develop a reporting Portal :- Enables organization to embed reports directly into business application and web portals, enabling users to consume report  within the context of their business process.

·         Interactive sorting Capabilities :-  User can sort the report data depends on their requirement .

·         DrillDown Report:- Without any customization or programming , users can seamlessly drill through any combination of data in the database.

·         Intuitive display options:- User have different requirement and expectation when viewing and interacting with reports and a report format that works well for one format of data might not be appropriate for other kinds of data.

·         Window Security:- SSRS implements a flexible and role based security model to protect reporting service. That means  every employee is allowed access to data which the user role is entitled to.

What are the limitations or drawbacks of SSRS?

The SSRS 2008 R2 has some limitations:-

·         No print button is available to print the report

·         It is very hard to debug expression or custom code

·         It’s does not use page number or total page number in page body

·         There is no way to pass sub-reports values to main report

·         It is not possible to insert a sub-report into the page  header or page footer

·         Page header create extra spaces in next pages

What is reporting life cycle?

Reporting Service has three main phase :-

·         Development of reports(Developer):-  at first reports need to developed and it is done by developer.

·         Management of Reports(DBA) :- when report is developed DBA ensure the following thing :-

Security :- only authorized user should access the report

Execution :- how the report will be executed to optimize data source performance

Scheduling of Report :- report are executed on scheduling timing

·         Report Delivery (DBA + Developer) :-  When the report is developed and executed the report is transferred to the business users. They use it and if any bug or enhancements is required then report go back to developer(Development stage)

How to deploy SSRS Report?

We can deploy SSRS Report in three ways:-

1.      Using Visual Studio :- In visual Studio we can directly deploy the through Solutions Explorer by providing the report server url in project property  at target server URL.

2.      Using Report Server :- We can directly go to the report server and deploy the report by browsing the report from the disk location OF SERVER.

3.      Creating the Utility :-  Sql Server provides the facility to create a customize utility to deploy the report.

What is sub report?

Sub report is kind of child report which open in main report when main report loads. We can pass the parameter to sub report.

What is RDL file?

RDL stands for Report Definition Language . When we save the report then the file is saved as ReportName.rdl . It is xml file. This rdl file is used for  deploying  the report to report server.

What is SSRS in asp.net.

Sql Server Reporting Service is a server-based report generation software system from Microsoft. Reporting Service includes a complete set of tools for you to create ,manage, and deliver reports and API  that enables developers to integrate  or extend data and report processing in custom  application.

With reporting you can create interactive ,tabular, graphical ,or free from report s from relational , multidimensional , or xml-based data sources.Reports  can include rich  data visualization , including charts, maps, and sparklines. You can publish reports, schedule report processing , or access report on demand. You can select from a variety of viewing formats , export reports to other applications such as Microsoft excel , and  subscribe to published report . The report that you create can be viewed over a web based connection or as part of a Microsoft  windows application or sharepoint sites.

C# program to print Diamond using for loop.

Here is source code to print Diamond in c# using for loop. This c# code is compiled successfully and executed with Microsoft visual studio 2010.

class Program

    {

       

        static void Main()

        {

            int number, i, k, count = 0;

            Console.WriteLine("Enter no of rows........");

            number = Convert.ToInt32(Console.ReadLine());

            count = number - 1;

            for (k = 1; k <= number; k++)

            {

                for (i = 1; i <= count; i++)

                {

                    Console.Write(" ");

                }

                count--;

                for (i = 1; i <= 2 * k - 1; i++)

                    Console.Write("*");

                Console.WriteLine();

            }

            count = 1;

            for (k = 1; k <= number - 1; k++)

            {

                for (i = 1; i <= count; i++)

                    Console.Write(" ");

                count++;

                for (i = 1; i <= 2 * (number - k) - 1; i++)

                    Console.Write("*");

                Console.WriteLine();

            }

                Console.ReadKey();

         }

      

    }

Here is the output of the program.

        *

      ***

    *****

      ***
        *