Showing posts with label sql server. Show all posts
Showing posts with label sql server. Show all posts

Saturday, December 7, 2024

Dynamic Pivot with SQL in SQL Server

 In our example we have a Dynamic Survey form in the database which we would use to take survey of employees across an agency. Now when we have collected the survey we need to generate a report in pivot format which would give u one row per employee and in the columns we would get answers to each question. Interesting!

Lets look at the options available in SQL Server.

To pivot your query and transform it into a format where each row represents an employee and the columns contain answers for each question, you can use a PIVOT operation.

Steps:

  1. Aggregate the Data:

    • Use the base query to gather the employee data, question texts, and their corresponding answers.
  2. Prepare the Pivot:

    • Use the PIVOT operator to dynamically transform rows (questions and answers) into columns for each question.
If the questions are dynamic and you don't know them in advance, you need to dynamically generate the column list using dynamic SQL.

Handling Dynamic Questions:

If the list of questions is not fixed and changes frequently, you can use dynamic SQL to generate the column list automatically.


DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- Step 1: Generate the Column List Using FOR XML PATH
SELECT @Columns = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(QUESTION)
    FROM QUIZ_QUESTIONS
    WHERE QUESTIONID IN (
        SELECT DISTINCT QUESTIONID
        FROM QUIZ_ATTEMPT_RESULT
        WHERE QUIZASSIGNID IN (SELECT QUIZASSIGNID FROM QUIZ_ASSIGNMENT WHERE QUIZID = 1)
    )
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Step 2: Construct the Dynamic Pivot Query
SET @SQL = '
WITH BaseData AS (
    SELECT 
        EMPLOYEES.EMPLOYEEID,
        EMPLOYEES.FIRSTNAME + '' '' + EMPLOYEES.LASTNAME AS EmployeeName,
        QUIZ_QUESTIONS.QUESTION AS QuestionText,
        QUIZ_QUESTION_ANSWERS.ANSWER AS Answer
    FROM [dbo].[QUIZ_ASSIGNMENT]
    INNER JOIN EMPLOYEES
        ON EMPLOYEES.EMPLOYEEID = [QUIZ_ASSIGNMENT].EMPLOYEEID
    INNER JOIN [dbo].[QUIZ_ATTEMPT_RESULT]
        ON QUIZ_ATTEMPT_RESULT.QUIZASSIGNID = QUIZ_ASSIGNMENT.QUIZASSIGNID
    INNER JOIN [dbo].[QUIZ_QUESTION_ANSWERS]
        ON QUIZ_QUESTION_ANSWERS.ANSWERID = QUIZ_ATTEMPT_RESULT.ANSWERID
    INNER JOIN [dbo].[QUIZ_QUESTIONS]
        ON QUIZ_QUESTIONS.QUESTIONID = QUIZ_ATTEMPT_RESULT.QUESTIONID
    WHERE QUIZ_ASSIGNMENT.QUIZID = 1
)
SELECT *
FROM (
    SELECT 
        EmployeeName,
        QuestionText,
        Answer
    FROM BaseData
) AS SourceTable
PIVOT (
    MAX(Answer)
    FOR QuestionText IN (' + @Columns + ')
) AS PivotTable;
';

-- Step 3: Execute the Dynamic SQL
EXEC sp_executesql @SQL;


Sample Input Data:

NameQuestionAnswer
John SmithWhat is your name?John
John SmithWhat is your age?30
Jane DoeWhat is your name?Jane
Jane DoeWhat is your age?25


Sample Output Data:

NameWhat is your name?What is your age?
John SmithJohn30
Jane DoeJane25

Friday, April 8, 2011

SQL Server Management, error 29506

When installing SQL Server Management studio on Windows 7 64bit it gives an error with error code 29506. The problem is we need to start this setup as Administrator. Unfortunately when we right click on the setup file, there is no option to Run as Administrator in the context menu.

A work around to this problem is we start a Command prompt (Console) as Administrator, set directory path to the location of setup file, and type the setup file name and press enter to execute.

Now the setup will start as Administrator and will successfully complete.

Steps:

1. Downloaded setup file SQLServer2005_SSMSEE_x64.msi from Microsoft and saved on my local disk, in my case it is D:\Softwares\SSMSEE\

2. Open start menu and type "cmd" in the search box, you will see "cmd.exe" program

3. Now right click it and select Run as Administrator

4. First change drive by typing D: and press enter

5. Now change directory to setup location, type "cd D:\Softwares\SSMSEE\" and press enter

6. Now type setup file name "SQLServer2005_SSMSEE_x64.msi" and press enter

7. The setup will start and complete successfully. That's all

This might also be the case with Windows 7 with 32bit version, in that case same solution should work.

Friday, December 31, 2010

SQL Database Snapshot

Create Database Snapshot on Current database:

USE TEST

GO

DECLARE @name VARCHAR(1000),
@filename VARCHAR(1000),
@dbname VARCHAR(1000),
@dbssname VARCHAR(1000),
@dbssfilename VARCHAR(1000)

DECLARE @hour VARCHAR(2),
@minute VARCHAR(2)

SET @hour = DATENAME(HOUR,GETDATE())
IF LEN(@hour) = 1 SET @hour = '0'+@hour
SET @minute = DATENAME(MINUTE,GETDATE())
IF LEN(@minute) = 1 SET @minute = '0'+@minute

SET @dbname = Db_Name();
SET @dbssname = @dbname+'_data_'+@hour+@minute
SET @dbssfilename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\'+@dbssname+'.ss'

SELECT @name = [name], @filename = [filename]
FROM sys.sysfiles
WHERE groupid = 1;


--CREATE SNAPSHOT
EXEC( 'CREATE DATABASE ' + @dbname + '_dbss' +@hour+@minute + ' ON
( NAME =' + @name + ', FILENAME = '''+@dbssfilename +''')
AS SNAPSHOT OF '+@dbname);


Restore Database to a Database Snapshot:
USE master;
GO
-- Reverting TEST to TEST_dbss1717
RESTORE DATABASE TEST from
DATABASE_SNAPSHOT = 'TEST_dbss1717';
GO

Drop Database Snapshot:
USE master;
GO
DROP DATABASE TEST_dbss1717;

Friday, November 12, 2010

SQL Server 2005, Search string in Procedures and Functions

Use following sql script to search some string inside Stored procedure or User defined function, this is handy when you have lot of stored procedures and functions and you are looking for particular one, like i want to list procedures in which a specific Table has been used.

This query search in procedures and functions script stored in database schema.

USE DATBASENAME

GO

-- SYS COMMENTS
SELECT distinct so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE charindex('Search', text) > 0


--INFORMATION_SCHEMA.ROUTINES
SELECT routine_name, routine_type
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Search%'