|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
July 19th, 2005, 10:46 AM
|
|||
|
|||
|
Like a pivot table
I am new to SQL so please bare with me. I am trying to create collumns from rows of data. Is this possible in SQL and how do i go about doing it. What i am looking for is basically the same functionality as a pivot table in Excell or Access. Currently I am extracting the same data and creating Pivot tables with the data repetitively. Writing this in SQL would speed things Dramatically.
|
|
#2
July 19th, 2005, 11:24 AM
|
|||
|
|||
|
Can you give a specific example of data you'll be working with and the data you want out of it?
__________________
"A pawn is the most important piece on the chessboard -- to a pawn"
|
|
#3
July 19th, 2005, 02:45 PM
|
|||
|
|||
|
table_one
Part Number Serial Number SITE Qty A123 xyz 1USHB-S 1 A123 qrs 1USHB-U 1 A123 pno 1USBR-U 1 A123 pnq 1USBR-U 1 new_table Part Number 1USHB-U 1USHB-S 1USBR-U A123 1 1 2
|
|
#4
July 19th, 2005, 03:18 PM
|
|||
|
|||
|
Give this a shot:
Code:
SELECT
Part_Number
,SUM(CASE Serial_Number
WHEN '1USHB-S' THEN SITE_Qty
END) AS 1USHB-S
,SUM(CASE Serial_Number
WHEN '1USHB-U' THEN SITE_Qty
END) AS 1USHB-U
,SUM(CASE Serial_Number
WHEN '1USBR-U' THEN SITE_Qty
END) AS 1USBR-U
FROM
table_one
GROUP BY
Part_Number
If 1SDBR-U is just the end of a longer serial number (which it appears, but the spacing makes it hard to tell for sure), you'll need to extract it with SUBSTRING when you run it through the CASE statement.
|
|
#5
June 13th, 2007, 09:14 AM
|
|||
|
|||
|
Suggestion
There is a software package I use for something similar. I think it can handle exactly what you need.
It's better because it can import and refresh from a SQL query and show the results immediately in a pivot table. It has different views on the datasource, and charting. So if you change the pivot table fields, the charts will update immediately. You also have the option of saving it in their own file format and sending the document to other people. I know this is an old post, but I would suggest you give it a shot.. Well it blocked me from using the direct URL. The software is called numberGo. You'll be able to find it in Google. Maybe a moderator can put in the URL for me. Good luck! Dan
|
|
| Viewing: Dev Articles Community Forums > Databases > General SQL Development > Like a pivot table |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
