SQL Server Pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server Pivot table

Author

Topic

Blackghost
Starting Member

10 Posts

Posted - 2014-05-22 : 05:31:05

HI ALl

I'm New here.

I need some assistance, I have not worked in SQL for a while now and cannot seem to remember how to create a pivot that will work correctly for me.

Table Name = 71467AE6

SELECT 
    0D39C6B0 AS Company, DDD24E25 AS Date_Of_Birth, 
    74290EA9 AS Department, 2A7417DC AS Document_Type, 
    E3421C61 AS Name, 2B9CFA8C AS Surname,
    96990BE2 AS ID_Number, E6621D8A as Passport_No, 
    3744C28A AS Job_Title, 903EBB60 as Start_Date,
    9BBAD0CA AS End_Date, AE108F93 as Type
FROM 
    Documents
WHERE 
    96990BE2 = '254254756'

This is the result.

302,NULL,605,New,Employee,Form,Richard,Watts,254254756,0,NULL,NULL,NULL,NULL

302,NULL,605,Personal,Paticulars,Form,Richard,Watts,254254756,0,NULL,NULL,NULL,NULL

302,NULL,605,Advance,Richard,Watts,254254756,0,NULL,NULL,NULL,NULL

302,NULL,605,Employment,Agreement,Signed,Richard,Watts,254254756,0,NULL,NULL,NULL,NULL

302,NULL,605,Inteview,Record,Richard,Watts,254254756,0,NULL,NULL,NULL,NULL

302,NULL,605,leave,Application,Richard,Watts,254254756,0,NULL,NULL,NULL,NULL

302,NULL,605,Doctor,Certificate,Richard,Watts,254254756,0,NULL,NULL,NULL,NULL

302,NULL,605,Miscellaneous,Documents,Richard,Watts,254254756,0,NULL,NULL,NULL,NULL

Company, Date_Of_Birth, Department, Document_Type, Name Surname, ID_Number, Passport_No, Job_Title, Start_Date, End_Date Type,

What I would like to see is the ID number to be unique on 1 row with all the respective elements next to it

ID_Number, Company, Dept., Name, Surname, Doctype. DoctType, DocType, DocType,

Result
254254756, 302, 605, Richard, Watts, Leave Application, Doctor Certificate, Miscellaneous, etc., etc., etc.

I hope my question is clear?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-05-22 : 08:20:50

DECLARE @t TABLE(Company int,Date_Of_Birth DATETIME,Department VARCHAR(50)
,Document_Type VARCHAR(50), Name VARCHAR(50), Surname VARCHAR(50)
,ID_Number VARCHAR(50), Passport_No VARCHAR(50), Job_Title VARCHAR(50)
,Start_Date DATE,End_Date DATE, Type VARCHAR(50)
)
insert @t
SELECT 302,NULL,605,'New Employee Form','Richard', 'Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Personal Paticulars Form','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Advance','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Employment Agreement Signed','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Inteview Record','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'leave Application','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Doctor Certificate','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Miscellaneous Documents','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL

--Query
SELECT DISTINCT ID_Number, Company, Date_Of_Birth,Department ,Name ,
Surname ,Passport_No, Job_Title,Start_Date,
End_Date ,Type, STUFF((SELECT ','+Document_Type FROM @t WHERE ID_Number = Tmp.ID_Number FOR XML PATH('')), 1,1, '')
FROM @t Tmp

--
Chandu

Blackghost
Starting Member

10 Posts

Posted - 2014-05-22 : 09:56:29

HI bandi

this will not be practical for I only added a few records in my example, this table has over 10million records in.

I need a query that will do a straight Pivot on this table?

any ideas???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-22 : 11:27:29

pivot 10 million rows? How many columns and rows will be in your desired result set?

Be One with the Optimizer
TG

Blackghost
Starting Member

10 Posts

Posted - 2014-05-23 : 01:45:18

quote:
Originally posted by TG

pivot 10 million rows? How many columns and rows will be in your desired result set?

Be One with the Optimizer
TG

It will only be 12 Columns, and rows should not be more that 250 rows.

Blackghost
Starting Member

10 Posts

Posted - 2014-05-23 : 08:00:35

quote:
Originally posted by bandi

DECLARE @t TABLE(Company int,Date_Of_Birth DATETIME,Department VARCHAR(50)
,Document_Type VARCHAR(50), Name VARCHAR(50), Surname VARCHAR(50)
,ID_Number VARCHAR(50), Passport_No VARCHAR(50), Job_Title VARCHAR(50)
,Start_Date DATE,End_Date DATE, Type VARCHAR(50)
)
insert @t
SELECT 302,NULL,605,'New Employee Form','Richard', 'Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Personal Paticulars Form','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Advance','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Employment Agreement Signed','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Inteview Record','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'leave Application','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Doctor Certificate','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL union all
SELECT 302,NULL,605,'Miscellaneous Documents','Richard','Watts',254254756,0,NULL,NULL,NULL,NULL

--Query
SELECT DISTINCT ID_Number, Company, Date_Of_Birth,Department ,Name ,
Surname ,Passport_No, Job_Title,Start_Date,
End_Date ,Type, STUFF((SELECT ','+Document_Type FROM @t WHERE ID_Number = Tmp.ID_Number FOR XML PATH('')), 1,1, '')
FROM @t Tmp

--
Chandu

Hi bandi

Thank you very much for the assistance once again, I'm going to use your script, just a question is there any way that instead of having to put the data into the second part of the script, (insert @t
SELECT) I can just put and ID Number (Unique Number) in and it will populate the rest of the information?

I hope this question is clear?

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources