SQL Pivot Tables - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Pivot Tables

Author

Topic

doyler442
Starting Member

4 Posts

Posted - 2012-05-14 : 07:29:50

Hi

I've been asked to create an SQL pivot table at work, and to be honest I have not seen this stuff in a long time (never worked with SQL pivot tables in particular).

I was wondering does anyone have any good tutorials, and examples that might help me get brought up to speed on this? Maybe then i could start putting forward some solutions I come up with and see where I'm going wrong.

The problem itself is as follows:

I have a table with multiple records of companies, and these are all assigned to specific employees. The table looks something like this:

Record_ID Name Number Assigned_To Source_File
--------- ----- ------- ----------- ------------

I need to create a pivot table that displays the Assigned_To person, and how many Source_Files have been assigned to them.

Look forward to your responses.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 08:43:54

If all you are looking for is a two column result set with the names and how many are assigned, you don't need a pivot table. It can be done via a query like this:

SELECT
	[Assigned_To],
	COUNT(*) AS CountOfFilesAssigned
FROM
	YourTable
GROUP BY
	[Assigned_To];

Or, are there more details you need to provide? If there is possibility that there are multiple rows for the same combination of Assigned_to and Source_file, you would need to make slight change to your query like this:

SELECT
	[Assigned_To],
	COUNT(DISTINCT Source_File) AS CountOfFilesAssigned
FROM
	YourTable
GROUP BY
	[Assigned_To];

doyler442
Starting Member

4 Posts

Posted - 2012-05-15 : 04:32:51

That actually works perfectly, but as ever they have asked to change it slightly.

What if I want to display the name of the Source_File also, of which there are three types of names? i.e. SO47A, SO50B

So what is displayed is the Assigned_To, and also all the files that are associated with them.

I'VE ACTUALLY MANAGED TO GET THIS GOING, THANKS VERY MUCH FOR YOUR HELP HERE.

doyler442
Starting Member

4 Posts

Posted - 2012-05-15 : 04:57:03

I also have another task that has more to do with a pivot (I think):

I have two tables, Master_List and Script_Results, and both of these contain a number of fields.

In the Master_List there are a list of suppliers, and they have a Record_ID.

In Script_Results, the records relate to the Record_ID above, but different data is captured.

What I require is to bring these two tables together, displaying all the fields. it has been suggested to me to use a pivot table - is this the best way of achieving what I need? Or is there a better way?

Thanks for your help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-15 : 06:56:20

quote:
Originally posted by doyler442

I also have another task that has more to do with a pivot (I think):

I have two tables, Master_List and Script_Results, and both of these contain a number of fields.

In the Master_List there are a list of suppliers, and they have a Record_ID.

In Script_Results, the records relate to the Record_ID above, but different data is captured.

What I require is to bring these two tables together, displaying all the fields. it has been suggested to me to use a pivot table - is this the best way of achieving what I need? Or is there a better way?

Thanks for your help.

Hard to tell without seeing the schema of the tables and some sample data. If you simply want to get a result set with all the fields in the two tables, you can join the two tables and do a select.

SELECT
	*
FROM
	Master_List m
	INNER JOIN Script_Results r
		ON  r.Record_id = m.Record_id;

You may need to make various tweaks to it - for example, if there would be records in the Master_list that do not have corresponding records in Script_Results and you still want to display those, you would use a LEFT JOIN instead of INNER JOIN.

doyler442
Starting Member

4 Posts

Posted - 2012-05-15 : 07:54:43

Yeah you were right again, they just had the wording wrong. This works perfectly, thanks very much for your help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-15 : 08:25:35

You are quite welcome.

One thing you might want to do is to replace the "SELECT *" with explicit column names, as in "SELECT m.Col1, m.Col2,...r.Col1..." etc. It is generally recommended best practice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources