Hi folks,
I have a table example:
Date Team Score
200801 A 12
200801 B 14
200802 A 3
200803 C 5
200803 A 5
Date and team are not hardcoded, next week team D could be added etc.
I'd like to convert (pivot?) to
Date A B C
200801 12 14
200802 3
200803 5 5
is there a simple approach to this?
any help appreciated. Sorry don't know what this type of conversion is called.
thanks.
7 8744
Could you maybe explain what the result should represent?
ck9663 2,878
Recognized Expert Specialist
Maybe this helps.
-- CK
Yeah I've been reading through the online docs etc., and some nice books like 'SQL Cookbook' and 'the Art of SQL'. The problem with the example in the linked page is that '[164] as emp1 ... as emp2' the columns are hardcoded into the sql statement. Since the team in each row (A, B, C, etc.) will later include other teams, this won't work for me.
I can write some code to create new sql statements each time by a first pass to determine unique teams, then use a case statement, just thought there'd be a cleaner approach.
Could you maybe explain what the result should represent?
Sure!
The result is grouped by row.col1 (date), with row.col2 the new col1->n, and row.col3 the data.
If it helps, think of it as total points each month. In Jan Team A had 12 points. In Jan Team B had 14 points. In Feb Team A had 3. In March Team C joined and had 5 points. In March Team A had 5 points.
I don't want to hard code the teams into record columns as these will change over time and would require recoding. Could do T1->t20 etc. but that still forces limits, gotta recode for team 21.
Probably wasn't obvious from the previous message, here is the desired output again: Note I could end up with 1000's of records to process, maybe 20 or so teams. I added 0's instead of spaces to help display, either (0's or spaces) is okay.
Date__ | A | B | C |
200801 | 12 | 14 | 0 |
200802 | 3 | 0 | 0 |
200803 | 5 | 0 | 5 |
Well I'm about 90% there perhaps.
SELECT *
FROM
(Select TDate, Team, Score from Test) tts
PIVOT
(
SUM(Score) FOR Team IN (A,B,C)
) as pvt
Again only problem is the (A,B,C) I'm looking for a wildcard or something I can use here so I don't have to preselect the column names.
MS Access Wizard gave me this:
TRANSFORM Sum(Test.Score) AS SumOfScore
SELECT Test.TDate
FROM Test
GROUP BY Test.TDate
PIVOT Test.Team;
which doesn't require the columns names, but this doesn't translate to mssql.
Any other suggestions?
Try this store procedure..
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS ON
if exists(select name from sysobjects where name = '##pivotos' and type= 'U')
DROP TABLE ##pivotos
Print('SELECT ' + @pivot + ' AS pivot INTO ##pivotos FROM ' + @table + ' WHERE 1=2')
exec ('SELECT ' + @pivot + ' AS pivot INTO ##pivotos FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivotos SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='',
@sumfunc=stuff( @sumfunc, len(@sumfunc), 1, ' END)' )
--print(@sumfunc)
SELECT @delim=CASE Sign( CharIndex('char ', data_type)+Char Index('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.informat ion_schema.colu mns
WHERE table_name='##p ivotos' AND column_name='pi vot'
SELECT @sql=@sql + '''' + convert(varchar (100), pivot) + ''' = ' +
stuff(@sumfunc, charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar (100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivotos
DROP TABLE ##pivotos
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@ select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
Try this store procedure..
...=stuff(@sele ct, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
I get incorrect syntax near the keyword 'pivot', line 31, then 34
tried changing a few things (pivot to piv so not reserved word etc.) but sol.
Will take me a while to figure out what this routine is doing. I take it this is a generic procedure you use? Does it work as posted for you? I'm using MS SQL 2005
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Michael John |
last post by:
Dear Oracle Developers,
my task is to make up a Oracle View from a Pivot table in MS Access.
Given are two tables to join:
T_FIRM:
FIRM_ABBR VARCHAR2(3 BYTE),
FIRM_LONG VARCHAR2(70 BYTE),
CONSTRAINT PK_FIRM PRIMARY KEY (FIRM_ABBR)
|
by: Alan Lane |
last post by:
Hello world:
I'm including both code and examples of query output. I appologize if
that makes this message longer than it should be.
Anyway, I need to change the query below into a pivot table query. I'm
having trouble doing it. Help!
Here is my code so far:
Sub OldRegionQuery()
|
by: Ajay |
last post by:
Hello all,
I used to display reports in Excel earlier on my website. Now the client has requested that he would like see reports in Pivot table on the web . The backend is Sql2000. Can you please help in determining hoe we can create a pivot table and display records in it using C#?
Thanks
Ajay
|
by: Grey |
last post by:
I have created a asp.net form for user to input data. After input the data,
user need to click a button to export the input data to excel for data
analysis with excel pivot table function. is it possible to do that?? if so,
any sample for me as i am a new in designing pivot table with asp.net
million thanks
|
by: Tim_k |
last post by:
Does anyone have an example of how to export a datagrid to an Excel pivot
table? The code below exports the grid contents to Excel using the Response
object. I'd like to expand it to show the data in a pivot table. Avoiding a
3rd party control is preferable.
Thanks,
Tim
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
| |
by: kingster |
last post by:
Hi,
I have a regular dataset and all i want to do is make a pivot table
display in a browser with the datasource of the pivot table to be this
dataset and then the end-user will be able to do whatever they want ...
i dont need to do any special formatting just a straigh ot pivot tables
usign sql server, asp.net, vb.net, OWC 10
|
by: Jerome Ranch |
last post by:
Okay
So I've got pivot tables setup in Access 2003.
Only about 30K records in the current 2005 databases...the pivots
summarize the info in a number of nice ways.
I need to get the pivot tables into a document so I can distribute the
tables, as is, as an electronic report (without the detailed data)
So I export to rtf and xls, and I get an error that there is a too
much information. I don't want all the data, just the summary table!
|
by: PeteCresswell |
last post by:
I've got something called "Reference Rates".
The idea is that on a given day, we have various rates of return for
various entities.
e.g. Libor 3-month return, Libor 6-month return, US Treasury Bonds, the
Prime rate, and so-forth.
We associate a security with one of those rates.
There are a set of rates for each calendar day, and the rates for that
|
by: dotnetguru |
last post by:
Hi SMART GUYS,
Please help me write a query. Actually I want to convert my rows into columns. Can anyone kindly give me the query to do it? My rows are about employees. There can be any number of employees (in the following table I have 3 employees ABC, EFG, WRI) with any number of records for each employee (there can be 20, 30 records each), but all employees have the same number of records (in the following table each employee has 3...
|
by: das |
last post by:
Hello all,
I have tried this in SQL 2000 with no luck, with pivot table
feature in 2005 I see some hope. Here is my problem, I have a table
with n rows. data within these is simple varchar. I want to pull the
data in these rows and create a table.
Ex:
Table1 data looks like this (actual table is much much bigger):
# columnName
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |