[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to have row data as columns SQL Query

Posted on 2011-02-23
3
Medium Priority
?
361 Views
Last Modified: 2012-05-11
Hi,
I have a table with sample data:

GroupID, TaskID, LaborID, QTY
1, Fix Bulbs, 3, 5
1, Fix Stairs, 3, 2
1, Fix XYZ, 4, 1

I want to write a query that would display the LaborID as columns, and the TaskID as the rows.
I suppose this is Pivot, but I am not clear. Been a long month.
0
Comment
Question by:melegant99
  • 2
3 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 34964332
simple try this
SELECT TaskID, [1],[2],[3],[4],[5],[6]
FROM (

		SELECT TaskID, LaborID 
		FROM yourTable	
	) P
	PIVOT
	(
		MAX(LaborID) FOR LaborID IN ([1],[2],[3],[4],[5],[6])
	) AS pvt
ORDER BY TaskID

Open in new window

0
 

Author Comment

by:melegant99
ID: 34964539
I don't know what the LaborID's will be though in advance, it could be any varying amount and ID #.
0
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 34964565
then you should create the pivot sql dynamically...

just create this part [1],[2],[3],[4],[5],[6] from the table by using a query that will include all the values.. and concat it twith the given query and execute it...

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Viewers will learn how the fundamental information of how to create a table.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question