pivot information: below

Showing posts with label below. Show all posts

Monday, March 26, 2012

Please help

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.
It's not all that pretty, but the following function will do the job,
there may be more efficient approaches, but this one does work.
--============================================
CREATE FUNCTION JoinRows(@.id int)
RETURN varchar(50)
AS
BEGIN
DECLARE @.f1 char(1)
DECLARE @.str varchar(50)
SET @.f1 = ''
SET @.str = ''
WHILE 1=1
BEGIN
SELECT top 1 @.f1 = f1
,@.str = @.str + f1 + ','
FROM tab2
WHERE id = @.id
AND f1 > @.f1
IF @.@.rowcount = 0 BREAK
END
IF LEN(@.str) > 1
SET @.str = SUBSTRING(@.str,1,len(@.str)-1)
RETURN @.str
END
--============================================
-- This select query will then return Tab3
SELECT
*, dbo.JoinRows(id)
FROM tab1
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <u4oLJnfxFHA.736@.tk2msftngp13.phx.gbl>, dkrreddy@.hotmail.com
says...
> Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
> matching values.
> Tab3 should have the info as below.
> Tab1
> --
> ID Val1 Val2
> 1 qqq sa
> 2 aaa fa
> 3 ddd we
>
> Tab2
> --
> ID F1
> 1 A
> 1 B
> 1 C
> 1 D
> 2 F
> 2 G
> 3 H
>
> Output Should be as blelow, without using the functions please advise the
> sql query.
> Tab3
> --
> ID Val1 Val2 F1
> 1 qqq sa A,B,C,D
> 2 aaa fa F,G
> 3 ddd we H
>
> Thanks in advance.
>
>
|||The following will work as well:
declare @.f1str varchar(100)
set @.f1str = ''
select @.f1str = @.f1str + f1 + ','
from F1
select @.f1str
You'd have to run it an ID at a time in a function but at least it's
only one execution per ID as opped to F1.
Hope that helps.

Friday, March 23, 2012

Please help

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
--
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
--
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
--
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.It's not all that pretty, but the following function will do the job,
there may be more efficient approaches, but this one does work.
-- ========================================
====
CREATE FUNCTION JoinRows(@.id int)
RETURN varchar(50)
AS
BEGIN
DECLARE @.f1 char(1)
DECLARE @.str varchar(50)
SET @.f1 = ''
SET @.str = ''
WHILE 1=1
BEGIN
SELECT top 1 @.f1 = f1
,@.str = @.str + f1 + ','
FROM tab2
WHERE id = @.id
AND f1 > @.f1
IF @.@.rowcount = 0 BREAK
END
IF LEN(@.str) > 1
SET @.str = SUBSTRING(@.str,1,len(@.str)-1)
RETURN @.str
END
-- ========================================
====
-- This select query will then return Tab3
SELECT
*, dbo.JoinRows(id)
FROM tab1
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <u4oLJnfxFHA.736@.tk2msftngp13.phx.gbl>, dkrreddy@.hotmail.com
says...
> Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
> matching values.
> Tab3 should have the info as below.
> Tab1
> --
> ID Val1 Val2
> 1 qqq sa
> 2 aaa fa
> 3 ddd we
>
> Tab2
> --
> ID F1
> 1 A
> 1 B
> 1 C
> 1 D
> 2 F
> 2 G
> 3 H
>
> Output Should be as blelow, without using the functions please advise the
> sql query.
> Tab3
> --
> ID Val1 Val2 F1
> 1 qqq sa A,B,C,D
> 2 aaa fa F,G
> 3 ddd we H
>
> Thanks in advance.
>
>|||The following will work as well:
declare @.f1str varchar(100)
set @.f1str = ''
select @.f1str = @.f1str + f1 + ','
from F1
select @.f1str
You'd have to run it an ID at a time in a function but at least it's
only one execution per ID as opped to F1.
Hope that helps.sql

Please guide - especially about Time Dimension and approach in general

Hi,

I have a table which contains all the transaction details for which I am trying to create a CUBE... The explanation below in brackets is only for clarity about each field. Kindly note that I am using the following table as my fact table. Let's call it tblFact

This table contains fields like transaction date, Product (which is sold), Product Family (to which the Product Belongs), Brand (of the product), Qty sold, Unit Price (at which each unit was sold), and some other fields.

I have created a Product dimension based on tblFact. I don't know if this is a good idea or not :confused: Is it okay and am I on the right track or should I base my Product Dimension on some other table (say tblProducts and then in the Cube editor link tblProducts with tblFact based on the ProductID field? Please guide.

Now coming to my last question:
Currently I am also creating my Time Dimension based on tblFact. Is this also a wrong approach?
1. Should I instead create the Time Dimension based on a different table (say tblTime) and again link up tblTime and tblFact in the Cube editor?

2. if yes, then how do I create tblTime from tblFact in a manner that it only contains all the transaction dates.

3. Assuming that I should take the tblTime approach, then should this table (tblTime) also contain the ProductID - representing the Product which was sold for each date in tblTime?

I realize that this is a lenghty post but reply and more importantly guidance from the experienced users will be greatly appreciated becuase I have recently started learning/playing around on the OLAP side of things and I know that this is the time that I get my foundations correct otherwise I'll end up getting used to some bad practice and will find it difficult to change my approach to cube designing later down the road.

So many thanks in advance and I eagerly look forward to reply from someone.No worries mate,

This is what the forum is for...

Ok - Down to what you need to do

When doing the design for a cube I always do a bit of anyalsis first. Looks like you have crack this bit. You know what your dimensions are - Time , product , brand etc This is what you should group on to build your fact table.

Your facts are going to be Qty Sold , Price. This is what you will be suming on with the SQL to build your fact table.

You said "should I base my Product Dimension on some other table (say tblProducts and then in the Cube editor link tblProducts with tblFact based on the ProductID field? Please guide."

This is exactly what a good cube design is based on mate.

I presume the basis of your fact table is a transactions type table.
First thing you need to do is build all your dimension tables.

A table for product dimension table should look something like :

create table tblProduct
(prod_id tinyint ,
prod_txt varchar (255)
)

Don't forget to put in a id for unknown product - just in case you get these in your base transaction table

Build the rest of your dimension tables like this and assign a tinyint composite key to each dimension. What you what is your fact table to be as small as possible in terms of datatypes.

Now once you have this you want to build your fact table.

What you do is take your fact table and join to each of you dimension tables (should be a left outer join) and sum on the qty and unit price and group up accross all your dimensions.

This now should be you fact table that you can reference in Anyalsis Manager. You will have to define all this with in here as well.

Whoo, that was an effort.

Any problems , questions give me a shout

Cheers|||Hi aldo_2003,

Many thanks for the reply. It answers a good number of my questions. Can you kindly advise regarding the remaining question i.e. the quoted portions below:

Build the rest of your dimension tables like this and assign a tinyint composite key to each dimension. What you what is your fact table to be as small as possible in terms of datatypes.
Question: By composite key do you mean define a Primary key in each table? I will do so but was planning to define the data type for my ProductID field (for example) in my tblProducts as int. However I'll follow your advise and instead use the datatype tinyint. Thanks for the tip :)

And my last question hopefully:
Now coming to my last question:
Currently I am also creating my Time Dimension based on tblFact. Is this also a wrong approach?
1. Should I instead create the Time Dimension based on a different table (say tblTime) and again link up tblTime and tblFact in the Cube editor?

2. if yes, then how do I create tblTime from tblFact in a manner that it only contains all the transaction dates.

3. Assuming that I should take the tblTime approach, then should this table (tblTime) also contain the ProductID - representing the Product which was sold for each date in tblTime?

I think Part 2 (above) is easy and all I have to do is make a copy of tblFact but this copy (say tblTime) will only contain the transaction date column (from tblFact). Kindly confirm my understanding.

However it's the answer to part 1 (above) and especially the part 3 above that is requested.

Looking forward to your reply.|||Your welcome,

forgot about the time question

what you want to do is create a tblTime dimension table

should basically have the grandularity that you want to use

you can find scripts on the net that will help you create and manage a time dimension table.

table should look like

create table tblTime
(date_time smalldatetime,
quarter tinyint,
month tinyint,
week tinyint ,
day int
)

populate this table with all the dates in your date range i.e

Jan 1999 12:00am to Jan 2009 12:00am

This is now your time dimension table.
Using anyalsis manager join back on to the fact table.
Anyalsis manager should guide you through the process of creating a time dimension.

Hope this helps

p.s the only reason I used a tinyint is that I assumed you would have no more than 255 products, if you have more then up the datatype to what you need

Cheers|||Hi again,

Don't mean to "over-flatter" but your replies REALLY have been of great help... Here I was trying to build everything (the fact as well as the dimensions) using only a single table and now I am quite clear regarding what's the right approach :)

3 last questions please :rolleyes:

you can find scripts on the net that will help you create and manage a time dimension table.
table should look like

create table tblTime
(date_time smalldatetime,
quarter tinyint,
month tinyint,
week tinyint ,
day int
)

That's another new tip :) Can u kindly guide where I can get these scripts from? I am assuming that these scripts will not only create the table (in a similar structure as you have suggested above) but will also populate the table with the all the desired date ranges e.g. Jan 1999 12:00am to Jan 2009 12:00am.

2nd last question: So my approach which I was assuming for creating tblTime (for the Time dimension was incorrect) i.e. I thought that this will simply contain the ALL the transaction dates from the transaction table (as I described in my previous post). But from your reply my understanding is that this approach is wrong.

and the last question: So the tblTime does not have to store the ProductID?

Sorry for all the botheration.|||No worries buddy

Glad to be of help

The time dimension table is stand alone and does not have to contain any other info other than time info.

I'll see if my collegue know and get back to you

Cheers|||http://www.databasejournal.com/features/mssql/article.php/10894_1466091_6

http://www.winnetmag.com/Article/ArticleID/23142/Windows_23142.html|||Originally posted by aldo_2003
The time dimension table is stand alone and does not have to contain any other info other than time info.

I'll see if my collegue know and get back to you

Cheers

Thanks aldo : for the reply, the link to articles, and also for the clarification about the time dimension's underlying table containing only time related information.

Kindly do let me know if you get a script which not only creates the table for time but also populates it ...

Bless you!|||Check this link http://www.winnetmag.com/Articles/ArticleID/41531/pg/4/4.html for any help.|||Originally posted by Satya
Check this link http://www.winnetmag.com/Articles/ArticleID/41531/pg/4/4.html for any help.

Hi Satya,

Many sincere thanks for the article. I went through it...

However at this stage I am learning the basics (as evident from this post and my questions to aldo) therefore I found he article to be real "HEVY STUFF" and very difficult to digest at the moment. I talks about the MDX world and that's something that I have yet to explore... I know I will have to get into MDX soon but have enough of the basic to get right first :)

I however would like to request if you can help me with another post from me in this forum (Subject" Need help to create my Time Dimension")... it's somewhat related to portion of the discussion in this post but I did not want to unnecessarily prolong this particular thread/post...

Looking forward to your help in my other post.

Thanks again and regards.|||Joozh
Since this has been answered so well i just wanted to chime in and suggest some reading for you

The Data Warehouse Toolkit by Ralph Kimball (http://www.bestwebbuys.com/books/compare/isbn/0471200247/isrc/b-home-search)

this is the second edition of this book revised in 2002 and it is a must have for every OLAP develper.sql

please check this not null SQL String

the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.

"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

Any ideas how I could implement this more robustly?
cheers
MSorry, doesn't work that way.

You need a condition for each column|||Cheat. Execute:
"SELECT id, make, model
, CAST(workToBeDone1 AS VARBINARY(10)) AS w1
, CAST(workToBeDone2 AS VARBINARY(10)) AS w2
, CAST(workToBeDone3 AS VARBINARY(10)) AS w3
, CAST(workToBeDone4 AS VARBINARY(10)) AS w4
, CAST(workToBeDone5 AS VARBINARY(10)) AS w5
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"If the Cast() columns do not ALL show NULL as their value, then you have data in the offending column(s). Empty strings, and sometimes even the constant "NULL" have been known to sneak into tables when you do not expect them!

-PatP|||thanks guys.
I'm sure my version was working fine until the database seemed to put something invisible into the columns.
I tried your code Pat but it returns "ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal."

What does the 'as w1' part do?

my code looks like this:
"SELECT id, make, model, CAST(workToBeDone1 AS VARBINARY(10)) AS w1, CAST(workToBeDone2 AS VARBINARY(10)) AS w2, CAST(workToBeDone3 AS VARBINARY(10)) AS w3, CAST(workToBeDone4 AS VARBINARY(10)) AS w4, CAST(workToBeDone5 AS VARBINARY(10)) AS w5 FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"|||Drop the quotes from around the SQL statement for starters ;)

For the 'As w1' try running this

SELECT id As 'Example'
FROM vehicles|||thanks georgev
sorry, I missed a crucial bit re the quotes: SQLstring="Select..."
I'll have a play with your example and see if I get it.|||nope, sorry, couldn't figure out what I am supposed to do with your example George.|||Run the thing in QA and see if you notice something.
Basically it's giving the column an alias http://doc.ddart.net/mssql/sql70/sa-ses_3.htm - scroll down to columns_alias :p|||can't use QA on this, I have to run scripts on pages on the server.
Not sure why I need aliases.
My database columns seem to contain invisible data, is there a way to discover if the columns have any meaningful data in them? NULL seems to be a bit flakey

I need to find cars that need work done - i.e. someone has inputted something like: 'replace tyres' in one of the workToBeDone fields for a Volvo. but my search is returning every car in the database because it is seeing something in the columns. (I think!).

I tried casting as varchar(255) - made no difference|||The "as W1" simply assigns an alias to the column as GeorgeV observed. It appears that your ADO implementation doesn't like the aliases.

If Query Anylyzer (or its equivalent) is available, then I'd use it instead of writing/changing code to support your ADO implementation. Operative word being "should", you should be able to simply drop the column names and move on without them.

-PatP|||And by drop the column names we don't mean physically dropping the columns... Just remove the "As ..." from your SQL statement.

The reason the aliases were applied in the first place because as soon as you perform any function on a column it loses the reference to the column name (because it's not the same as the column data any more!). The Aliases allow us to access the columns by referenec in ADO (or so I believe).|||I dropped the aliases, but it made no difference, I'm still getting:
'Item cannot be found in the collection corresponding to the requested name or ordinal',|||Ok, let's try to solve the problem from a different vector and execute:"SELECT id, make, model
, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"-PatP|||Thanks Pat,
still getting the same error. here's more of the code (inc. your bit) to give you a bigger picture:

Set linkRS = Server.CreateObject("ADODB.Recordset")

salePrice = request.Form("salePrice")
make=request.Form("make")
model2show=request.Form("model2show")
salePrice=request.Form("salePrice")
fuel=request.Form("fuel")
sold=request.Form("sold")
workOutstanding=request.Form("workOutstanding")
notOnWebsite=request.Form("notOnWebsite")

strSQL="SELECT id, make, model, model2show, registration, price FROM vehicles WHERE price BETWEEN "& salePrice &""
if make <> "" then strSQL = strSQL & " AND make = '" & make & "'"

if fuel <> "" then strSQL = strSQL & " AND fuel = '" & fuel & "'"

if model2show <> "" then strSQL = strSQL & " AND model2show = '" & model2show & "'"

if sold = "yes" then strSQL = strSQL & " AND sold = 'yes'"

if workOutstanding = "yes" then strSQL = "SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

if notOnWebsite = "yes" then strSQL = strSQL & " AND active = 'no'"

strSQL = strSQL & " ORDER BY make"
'response.Write(strSQL)
linkRS.Open strSQL, oConn, 2, 3
if (linkRS.BOF and linkRS.EOF) then
response.Write("<p class=""inputRed"">No vehicles to display - try selecting fewer parameters</p>")
else
linkRS.moveFirst
Do while not linkRS.eof
make = linkRS("make")
'etc.
'etc.

most of this works fine, but the error message is odd because those fields do exist.|||Uncomment your 'response.Write(strSQL) and post the result.
First glance suggests you have a problem with your BETWEEN statement|||here you go:
SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL ORDER BY make|||Maybe it contain spaces, try this
where coalesce(workToBeDone1,workToBeDone2,workToBeDone3 ,workToBeDone4,workToBeDone5,'') != ''|||thanks,
same error msg tho'

Wednesday, March 21, 2012

Please advise

Tuesday, March 20, 2012

Plagued by corruption

Since we have migrated our 2000 databases to SQL 2005 on new hardware (described below), we have experienced re-occurring corruption on the same tables and indexes even after the corruption had been resolved by dropping/re-creating the indexes manually. Typical events include:

1. Clients receiving errors, such as 8646
2. We see the errors in the event logs, run DBCC's to confirm the errors which then creates a dump file
3. We resolve by dropping/creating the index(es) affected
4. Re-run the DBCC checks and they come up clean.

A day or two later, the entire cycle will repeat against the same object/index. This problem exists on multiple different databases on multiple different servers, all with the same structure and hardware. Our SAN engineers say there are no issues they can see with the disks.

I would be very appreciative for any suggestions to better troubleshoot the issue, or, of course, a resolution would be magnificent!!

Here is as much information about our setup and errors experienced as I can think of to provide. Of course, if I am missing something just shout and I will include it. TIA.

We are running SQL 2005 enterprise x64 sp1 on Windows 2003 enterprise x64 sp1 as a 2-node active/passive cluster (native windows clustering). The hardware is HP Proliant DL585, 4 dual-core processors @. 2.8 GHz, 48 GB of RAM.

When clustering the servers, we pay special attention to ensure the NIC settings are as specified by published MS white papers both at the NIC level and as cluster configurations. I can provide those settings if necessary.

Other pertinent settings:
- We set the SQL Server's min/max setting to both be 40960 MB (40GB)
- MAXDOP to 2
- System DB's share two drives, one for data, one for log (all drives are RAID or 10)
- TempDB has 1 drive for data, one for log, and we modify TempDB to have as many data files as processors
- User DBs share multiple pairs of drives, each pair has a dedicated data drive and dedicated log drive
- Backups (litespeed) go to a dedicated drive
- Full-text catalogs go to a dedicated drive
- Maintenance jobs run every night and include: Reindex, update stats, backups and other miscellaneous jobs specific to our environment
- We set physical database files to grow by 200MB increments when over 2GB in size and we proactively grow each file overnight if the file's free space is < 10%Additional error and dump file info...

The typical app log entry for error 8646:

Unable to find index entry in index ID 1, of table 894626230, in database 'dbname'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

Contents of the dump file:

This file is generated by Microsoft SQL Server
version 9.00.3042.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.

Computer type is AT/AT COMPATIBLE.
Current time is 07:32:39 11/07/07
8 Unknown CPU 9., 2813 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory
MemoryLoad = 92%
Total Physical = 49149 MB
Available Physical = 3913 MB
Total Page File = 60149 MB
Available Page File = 15932 MB
Total Virtual = 8388607 MB
Available Virtual = 8338218 MB

DBCC RESULTS
-------
<DbccResults>
<Dbcc ID="0" Error="2511" Severity="16" State="1">Table error: Object ID 894626230, index ID 1, partition ID 7205
7596565782528, alloc unit ID 72057596808986624 (type In-row data). Keys out of order on page (1:487717), slots 3
and 4.</Dbcc>
<Dbcc ID="1" Error="2511" Severity="16" State="1">Table error: Object ID 894626230, index ID 1, partition ID 7205
7596565782528, alloc unit ID 72057596808986624 (type In-row data). Keys out of order on page (1:487717), slots 79
and 80.</Dbcc>

...and so on...|||and last but not least...

* Short Stack Dump

PSS @.0x000000045399FA80
--------

CSession @.0x000000045399E410
---------
m_spid = 54 m_cRef = 14 m_rgcRefType[0] = 1
m_rgcRefType[1] = 1 m_rgcRefType[2] = 11 m_rgcRefType[3] = 1
m_rgcRefType[4] = 0 m_rgcRefType[5] = 0 m_pmo = 0x000000045399E080
m_pstackBhfPool = 0x0000000000000000 m_dwLoginFlags = 0x83e0 m_fBackground = 0
m_fClientRequestConnReset = 0 m_fUserProc = -1 m_fConnReset = 0
m_fIsConnReset = 0 m_fInLogin = 0 m_fAuditLoginSent = 1
m_fAuditLoginFailedSent = 0 m_fReplRelease = 0 m_fKill = 0
m_ulLoginStamp = 54267395 m_eclClient = 5 m_protType = 5
m_hHttpToken = FFFFFFFFFFFFFFFF

m_pV7LoginRec
-------
0000000000000000: d60b0000 02000972 00100000 00000007 08190000 ......r.........

0000000000000014: 00000000 e0830000 2c010000 09040000 5e000c00 ......,......^...

0000000000000028: 00000000 00000000 76000700 84000000 00000000 ......v.........

000000000000003C: 84000400 8c000000 8c000000 00163582 4ea48c00 ...........5.N...

0000000000000050: 4a0b8c00 00000000 00000000 0000J..........

CPhysicalConnection @.0x000000045399E2E0
------------
m_pPhyConn->m_pmo = 0x000000045399E080
m_pPhyConn->m_pNetConn = 0x000000045399EB00
m_pPhyConn->m_pConnList = 0x000000045399E3E0
m_pPhyConn->m_pSess = 0x000000045399E410 m_pPhyConn->m_fTracked = -1
m_pPhyConn->m_cbPacketsize = 4096 m_pPhyConn->m_fMars = 0 m_pPhyConn->m_fKill = 0

CBatch @.0x000000045399EFD0
--------
m_pSess = 0x000000045399E410 m_pConn = 0x000000045399EEC0 m_cRef = 3
m_rgcRefType[0] = 1 m_rgcRefType[1] = 1 m_rgcRefType[2] = 1
m_rgcRefType[3] = 0 m_rgcRefType[4] = 0 m_pTask = 0x00000000038085C8

EXCEPT (null) @.0x00000000507BA138
----------
exc_number = 0 exc_severity = 0 exc_func = 0x0000000001873F50

Task @.0x00000000038085C8
--------
CPU Ticks used (ms) = 6134 Task State = 2
WAITINFO_INTERNAL: WaitResource = 0x0000000000000000 WAITINFO_INTERNAL: WaitType = 0x0
WAITINFO_INTERNAL: WaitSpinlock = 0x0000000000000000 SchedulerId = 0x2
ThreadId = 0x1cc0 m_state = 0 m_eAbortSev = 0

EC @.0x000000045399FA90
-------
spid = 54 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x0 ec_atomic = 0x0 __fSubProc = 1
ec_dbccContext = 0x00000000507BEF50 __pSETLS = 0x000000045399EF30 __pSEParams = 0x000000045399F350
__pDbLocks = 0x000000045399FEE0

SEInternalTLS @.0x000000045399EF30
----------
m_flags = 0 m_TLSstatus = 3 m_owningTask = 0x00000000038085C8
m_activeHeapDatasetList = 0x000000045399EF30
m_activeIndexDatasetList = 0x000000045399EF40

SEParams @.0x000000045399F350
---------
m_lockTimeout = -1 m_isoLevel = 4096 m_logDontReplicate = 0
m_neverReplicate = 0 m_XactWorkspace = 0x0000000BA3DDB9D0
m_pSessionLocks = 0x0000000780BE6F10 m_pDbLocks = 0x000000045399FEE0
m_execStats = 0x0000000082256E40 m_pAllocFileLimit = 0x0000000000000000|||Do the machines have ECC RAM?|||Yes, all our servers have ECC RAM, which I believe is a req. for the HP servers.

placing two matrix data regions one below the other without spacin

On my report I want to place two matrix data regions, whereas the second
matrix data region is placed exactly below the first matrix data region -
without spacing.
The first matrix shows the data with the numbers for the year 2006 and the
second matrix shows exactly the same data but with the numbers for the year
2005 ! So I donâ't want to show the column header for the second matrix data
region again. Therefore I set the visibility of the column header textboxes
to hidden and the column header is not shown as expected.
But a spacing remains between the two matrix data regions and I donâ't know
how to to eliminate it. The spacing has the size (height) of the hidden
column header text boxes. Also rendering the report in Excel doesnâ't give the
correct result. I also want to avoid overlapping report items because this
will probably produce rendering problems too.
Who can help to get rid of the remaing spacing between the two matrix data
regions !?
Any hints are welcome !Try selecting the whole row of your column header, and set the Height (not
LineHeight) to 0.01cm (or whatever your measurements are.)
Also, make the background colour for the row the same as the cells under it,
making it look like the top cells are slightly bigger than the rest.
Kaisa M. Lindahl Lervik
"Timmo" <Timmo@.discussions.microsoft.com> wrote in message
news:A1D86284-DBE1-4BE8-9881-CED568E6D938@.microsoft.com...
> On my report I want to place two matrix data regions, whereas the second
> matrix data region is placed exactly below the first matrix data region -
> without spacing.
> The first matrix shows the data with the numbers for the year 2006 and the
> second matrix shows exactly the same data but with the numbers for the
> year
> 2005 ! So I don't want to show the column header for the second matrix
> data
> region again. Therefore I set the visibility of the column header
> textboxes
> to hidden and the column header is not shown as expected.
> But a spacing remains between the two matrix data regions and I don't know
> how to to eliminate it. The spacing has the size (height) of the hidden
> column header text boxes. Also rendering the report in Excel doesn't give
> the
> correct result. I also want to avoid overlapping report items because this
> will probably produce rendering problems too.
> Who can help to get rid of the remaing spacing between the two matrix data
> regions !?
> Any hints are welcome !
>|||Try selecting the whole row of your column header, and set the Height (not
LineHeight) to 0.01cm (or whatever your measurements are.)
Also, make the background colour for the row the same as the cells under it,
making it look like the top cells are slightly bigger than the rest.
Kaisa M. Lindahl Lervik
"Timmo" <Timmo@.discussions.microsoft.com> wrote in message
news:A1D86284-DBE1-4BE8-9881-CED568E6D938@.microsoft.com...
> On my report I want to place two matrix data regions, whereas the second
> matrix data region is placed exactly below the first matrix data region -
> without spacing.
> The first matrix shows the data with the numbers for the year 2006 and the
> second matrix shows exactly the same data but with the numbers for the
> year
> 2005 ! So I don't want to show the column header for the second matrix
> data
> region again. Therefore I set the visibility of the column header
> textboxes
> to hidden and the column header is not shown as expected.
> But a spacing remains between the two matrix data regions and I don't know
> how to to eliminate it. The spacing has the size (height) of the hidden
> column header text boxes. Also rendering the report in Excel doesn't give
> the
> correct result. I also want to avoid overlapping report items because this
> will probably produce rendering problems too.
> Who can help to get rid of the remaing spacing between the two matrix data
> regions !?
> Any hints are welcome !
>|||Hi Kaisa !
Thank you very much for your answer ! Unfortunately your hint didn't help. I
already tried to set the height of the row with the column headers to 0 cm
and also setting the height to 0.01 cm couldn't help because the system sets
the height automatically to 0.07937cm which seems to be some minimal height !?
Nevertheless, if another idea comes to your mind, I would appriciate it a lot.
Regards
Timmo
"Kaisa M. Lindahl Lervik" wrote:
> Try selecting the whole row of your column header, and set the Height (not
> LineHeight) to 0.01cm (or whatever your measurements are.)
> Also, make the background colour for the row the same as the cells under it,
> making it look like the top cells are slightly bigger than the rest.
> Kaisa M. Lindahl Lervik
> "Timmo" <Timmo@.discussions.microsoft.com> wrote in message
> news:A1D86284-DBE1-4BE8-9881-CED568E6D938@.microsoft.com...
> > On my report I want to place two matrix data regions, whereas the second
> > matrix data region is placed exactly below the first matrix data region -
> > without spacing.
> > The first matrix shows the data with the numbers for the year 2006 and the
> > second matrix shows exactly the same data but with the numbers for the
> > year
> > 2005 ! So I don't want to show the column header for the second matrix
> > data
> > region again. Therefore I set the visibility of the column header
> > textboxes
> > to hidden and the column header is not shown as expected.
> >
> > But a spacing remains between the two matrix data regions and I don't know
> > how to to eliminate it. The spacing has the size (height) of the hidden
> > column header text boxes. Also rendering the report in Excel doesn't give
> > the
> > correct result. I also want to avoid overlapping report items because this
> > will probably produce rendering problems too.
> >
> > Who can help to get rid of the remaing spacing between the two matrix data
> > regions !?
> > Any hints are welcome !
> >
>
>

Monday, March 12, 2012

placing existing table on a different filegroup

Hi ,
Is there any commands to place existing tables to a new file group besides
using the EM as below ?
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table belongs, and then
click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Tables tab, in the Table Filegroup list, select the filegroup on whic
h
to place the table.
Optionally, in the Text Filegroup list, select a filegroup on which to place
any text, image, and ntext columns.
appreciate any advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1You can (re)create a clustered index for the table; the data will always
move to the FG that the clustered index is built on
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"maxzsim via webservertalk.com" <u14644@.uwe> wrote in message
news:5ff96f8c4fdba@.uwe...
> Hi ,
> Is there any commands to place existing tables to a new file group besides
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise
> Manager)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on
> which
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to
> place
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Hi ,
Another is for the Text FileGroup : i seem not able to change it to the new
filegroup it keeps turning back to the PRIMARY FileGroup even though i have
changed to a new filegorup and saved it
any possibly reason ?
tks & rdgs
maxzsim wrote:
>Hi ,
> Is there any commands to place existing tables to a new file group besides
>using the EM as below ?
>How to place an existing table on a different filegroup (Enterprise Manager
)
>To place an existing table on a different filegroup
>Expand a server group, and then expand a server.
>Expand Databases, expand the database in which the table belongs, and then
>click Tables.
>In the details pane, right-click the table, and then click Design Table.
>Right-click any column, and then click Properties.
>On the Tables tab, in the Table Filegroup list, select the filegroup on whi
ch
>to place the table.
>Optionally, in the Text Filegroup list, select a filegroup on which to plac
e
>any text, image, and ntext columns.
>appreciate any advise
>tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||It is interesting to see on Profiler what Enterprise Manager is doing when
you follow that procedure. Among other commands is doing
-- to move mytable to myfilegroup
create table tmp_mytable ( ... ) on myfilegroup
insert into tmp_mytable (select ... from mytable)
drop table mytable
sp_rename tmp_mytable mytable
Ben Nevarez, MCDBA, OCP
Database Administrator
"maxzsim via webservertalk.com" wrote:

> Hi ,
> Is there any commands to place existing tables to a new file group beside
s
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise Manage
r)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on wh
ich
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to pla
ce
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1
>|||tks
Kalen Delaney wrote:
>You can (re)create a clustered index for the table; the data will always
>move to the FG that the clustered index is built on
>
>[quoted text clipped - 20 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||hi ,
this is what i got from the profiler using the standard i.e no inclsion of
columns or new processes or any filter:
use [GL53_03_Bkup]
set implicit_transactions on SET TEXTSIZE 2147483647
sp_MStablechecks N'dbo.GL030106'
IF @.@.TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off SET TEXTSIZE 64512
use [GL53_03_Bkup]
select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (cas
e
when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.i
d,
N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id,
N'TableHasActiveFulltextIndex'), ObjectProperty(o.id,
N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id,
N'tableisfake')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.i
d,
N'IsQuotedIdentOn')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id,
N'IsAnsiNullsOn')=1) then 1 else 0 end) from dbo.sysobjects o, dbo.
sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and
i.
indid < 2 and o.name not like N'#%' and o.id = object_id(N'[dbo].[GL030106]
') order by s1, s2
SELECT N'Testing Connection...'
what can i actually make out of this ? or what other events i should be
included to see such changes ?
The table : Gl030106's original "Text FileGroup" is "PRIMARY" when i tried t
o
change it to my own FG : TEST2 and saved it goes back to PRIMARY
tks & rdgs
Ben Nevarez wrote:
>It is interesting to see on Profiler what Enterprise Manager is doing when
>you follow that procedure. Among other commands is doing
>-- to move mytable to myfilegroup
>create table tmp_mytable ( ... ) on myfilegroup
>insert into tmp_mytable (select ... from mytable)
>drop table mytable
>sp_rename tmp_mytable mytable
>Ben Nevarez, MCDBA, OCP
>Database Administrator
>
>[quoted text clipped - 17 lines]
Message posted via http://www.webservertalk.com

Friday, March 9, 2012

pk_dtproperties Question

I'm having some weird locking in SQL Server 2000 and I look the locks with a
modified sp_locks it shows thousands of the locks below. But I can't find
any code or SQL scripts that use that table and the table has 0 rows.
Has anyone seen this?
thanks
Johh
PrePressAutomation pk_dtproperties DB S
PrePressAutomation pk_dtproperties KEY Range
PrePressAutomation pk_dtproperties PAG IS
PrePressAutomation pk_dtproperties PAG IX
PrePressAutomation pk_dtproperties TAB IS
PrePressAutomation pk_dtproperties TAB Sch-SHi
pk_dtproperties is the primary key on dtproperties which is used for
database diagrams
http://groups.google.com/groups?hl=en&lr=&threadm=eyjuHEShEHA.2620%40TK2MSFTNGP10.phx.gbl&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26scoring%3Dd%26q%3Dpk_dtproperties
John
"John" wrote:
> I'm having some weird locking in SQL Server 2000 and I look the locks with a
> modified sp_locks it shows thousands of the locks below. But I can't find
> any code or SQL scripts that use that table and the table has 0 rows.
> Has anyone seen this?
> thanks
> Johh
> PrePressAutomation pk_dtproperties DB S
> PrePressAutomation pk_dtproperties KEY Range
> PrePressAutomation pk_dtproperties PAG IS
> PrePressAutomation pk_dtproperties PAG IX
> PrePressAutomation pk_dtproperties TAB IS
> PrePressAutomation pk_dtproperties TAB Sch-S
>|||Thank you John, but I don't have any database diagrams on that server. I
also tried to delete the table, but in SQL 2000 sp3a It's a system table and
will not let me delete it. Do you have any clues I'm getting locks on this
table?
Thank you
John
"John Bell" wrote:
> Hi
> pk_dtproperties is the primary key on dtproperties which is used for
> database diagrams
> http://groups.google.com/groups?hl=en&lr=&threadm=eyjuHEShEHA.2620%40TK2MSFTNGP10.phx.gbl&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26scoring%3Dd%26q%3Dpk_dtproperties
> John
> "John" wrote:
> > I'm having some weird locking in SQL Server 2000 and I look the locks with a
> > modified sp_locks it shows thousands of the locks below. But I can't find
> > any code or SQL scripts that use that table and the table has 0 rows.
> >
> > Has anyone seen this?
> > thanks
> > Johh
> >
> > PrePressAutomation pk_dtproperties DB S
> > PrePressAutomation pk_dtproperties KEY Range
> > PrePressAutomation pk_dtproperties PAG IS
> > PrePressAutomation pk_dtproperties PAG IX
> > PrePressAutomation pk_dtproperties TAB IS
> > PrePressAutomation pk_dtproperties TAB Sch-S
> >|||Hi John
I don't know why this should be. You may want to check the inputbuffer
for that spid http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8v8y.asp
You may also want to check what SQLAgent and NT jobs are scheduled,
and if there are any services.
Failing that you may want to see if stopping/starting the SQL Server
service
removes the problem.
John
"John" <John@.discussions.microsoft.com> wrote in message news:<2143B280-2453-4BB4-81F0-DAF461E1050A@.microsoft.com>...
> Thank you John, but I don't have any database diagrams on that server. I
> also tried to delete the table, but in SQL 2000 sp3a It's a system table and
> will not let me delete it. Do you have any clues I'm getting locks on this
> table?
> Thank you
> John
> "John Bell" wrote:
> > Hi
> >
> > pk_dtproperties is the primary key on dtproperties which is used for
> > database diagrams
> >
> > http://groups.google.com/groups?hl=en&lr=&threadm=eyjuHEShEHA.2620%40TK2MSFTNGP10.phx.gbl&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26scoring%3Dd%26q%3Dpk_dtproperties
> >
> > John
> >
> > "John" wrote:
> >
> > > I'm having some weird locking in SQL Server 2000 and I look the locks with a
> > > modified sp_locks it shows thousands of the locks below. But I can't find
> > > any code or SQL scripts that use that table and the table has 0 rows.
> > >
> > > Has anyone seen this?
> > > thanks
> > > Johh
> > >
> > > PrePressAutomation pk_dtproperties DB S
> > > PrePressAutomation pk_dtproperties KEY Range
> > > PrePressAutomation pk_dtproperties PAG IS
> > > PrePressAutomation pk_dtproperties PAG IX
> > > PrePressAutomation pk_dtproperties TAB IS
> > > PrePressAutomation pk_dtproperties TAB Sch-S
> > >

Wednesday, March 7, 2012

Pivot-related query

I have a couple of tables (see below) that tell me what days a particular
customer depot (LocationID) is scheduled to have deliveries on (DeliveryDays
table), and what days the Courier picks up for these deliveries
(CarrierCollections table)
I want to create a query (a view actually) that tells me for each depot,
which carrier collection days are possible/appropriate - this is to help the
people who are preparing the goods for despatch. The complication is that I
want one row for each Depot, eg:
LocationID, Mon, Tues, Weds, Thurs, Fri
8, null, 1, null, 1, null
9, null, null, null, 1, null
Pivots are fiddly enough as it is, but combining records in this way is
beyond me as yet. Has anyone got any ideas or solutions?
I can achieve the result I want by running one query within another (in my
ASP application) but this would be very inefficient and would significantly
slow down this page, so I would like to make SQL do the hard work so I only
need to do one query in my ASP app.
Thanks in advance...
Chris
Tables & Data:
CREATE TABLE [dbo].[CarrierCollections] (
[CarrierID] [int] NOT NULL ,
[CollectionDay] [tinyint] NOT NULL ,
[DeliveryDay] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeliveryDays] (
[LocationID] [int] NOT NULL ,
[DeliveryDay] [tinyint] NOT NULL
) ON [PRIMARY]
GO
Insert into DeliveryDays Values (8, 3)
Insert into DeliveryDays Values (8, 5)
Insert into DeliveryDays Values (9, 2)
Insert into DeliveryDays Values (9, 6)
Insert into CarrierCollections Values (119, 3, 4)
Insert into CarrierCollections Values (119, 3, 5)
Insert into CarrierCollections Values (119, 5, 2)
Insert into CarrierCollections Values (119, 5, 3)
Insert into CarrierCollections Values (119, 5, 6)
Note: 1 = Sunday, 7 = Saturday
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Something like:
SELECT d1.LocationID,
MAX( CASE c1.DeliveryDay WHEN 1 THEN 1 END ) AS "Sunday",
MAX( CASE c1.DeliveryDay WHEN 2 THEN 1 END ) AS "Monday",
MAX( CASE c1.DeliveryDay WHEN 3 THEN 1 END ) AS "Tuesday",
MAX( CASE c1.DeliveryDay WHEN 4 THEN 1 END ) AS "Wednesday",
MAX( CASE c1.DeliveryDay WHEN 5 THEN 1 END ) AS "Thursday",
MAX( CASE c1.DeliveryDay WHEN 6 THEN 1 END ) AS "Friday",
MAX( CASE c1.DeliveryDay WHEN 7 THEN 1 END ) AS "Saturday"
FROM CarrierCollections c1
INNER JOIN DeliveryDays d1
ON c1.DeliveryDay = d1.DeliveryDay
GROUP BY d1.LocationID ;
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uMs6WifEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Something like:
> SELECT d1.LocationID,
> MAX( CASE c1.DeliveryDay WHEN 1 THEN 1 END ) AS "Sunday",
> MAX( CASE c1.DeliveryDay WHEN 2 THEN 1 END ) AS "Monday",
> MAX( CASE c1.DeliveryDay WHEN 3 THEN 1 END ) AS "Tuesday",
> MAX( CASE c1.DeliveryDay WHEN 4 THEN 1 END ) AS "Wednesday",
> MAX( CASE c1.DeliveryDay WHEN 5 THEN 1 END ) AS "Thursday",
> MAX( CASE c1.DeliveryDay WHEN 6 THEN 1 END ) AS "Friday",
> MAX( CASE c1.DeliveryDay WHEN 7 THEN 1 END ) AS "Saturday"
> FROM CarrierCollections c1
> INNER JOIN DeliveryDays d1
> ON c1.DeliveryDay = d1.DeliveryDay
> GROUP BY d1.LocationID ;
Bingo!
Damn! I almost got this far but missed out the MAX() function. Seems obvious
in retrospect.
Thanks very much Anith.
Chris

Monday, February 20, 2012

Pivot Question

I want to pivot some data returned from a query. I've use the standard
technique [Sum(Case...)] below in the past, but in this situation it's not
ideal.
Typical solution:
SELECT
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2002
In my case, I'm listing the Parts that need despatching and how many are
needed of each. The first issue is that there are 30-50 parts typically, and
I don't think that 30-50 Sum(Case...) statements are the best solution.
Secondly (and more importantly), new parts are added and removed on a
regular basis, so hard-coding like this is pure folly.
What are the best alternatives to this? Are there any?
I have a way around this problem using my ASP code, but I'm interested in a
general solution for such a scenario for future purposes as well.
Thanks
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Am I missing something.
Grouping on part name or number seems the obvious solution.|||Am I missing something.
Grouping on part name or number seems the obvious solution.|||"andy" <aon14@.lycos.co.uk> wrote in message
news:1134388670.211751.172940@.o13g2000cwo.googlegroups.com...
> Am I missing something.
Either of us could be...

> Grouping on part name or number seems the obvious solution.
>
Yeah that gets me so far...
PartNo - Count:
Part1 - 10
Part2 - 20
Part3 - 15
Whereas I want:
Part1 - Part2 - Part3
10 - 20 - 15
This is a bit of a contrived example since the solution is easy enough to
achieve in my ASP code, but I've had examples in the past where the ASP
solution wasnt appropriate...
CJM|||The best solution is the one you've already cited; do it on the client
side. Your server will love you for it :)
However, if you must do it on the server side, google for "SQL Server
dynamic pivot" for various solutions to this problem. Most have
limitations, but they can do the trick. As an aside, SQL Server 2005
has a PIVOT command built-in.|||Hi,
Why not try a third party product
AGS Crosstab Builder for SQL 2000
www.ag-software.com
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OR6%23VEx$FHA.2784@.tk2msftngp13.phx.gbl...
>I want to pivot some data returned from a query. I've use the standard
>technique [Sum(Case...)] below in the past, but in this situation it's not
>ideal.
> Typical solution:
> SELECT
> SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
> ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
> ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
> ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
> ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
> ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
> ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
> ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
> ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
> ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
> ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
> ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
> FROM Sales.SalesOrderHeader
> WHERE YEAR(OrderDate) = 2002
> In my case, I'm listing the Parts that need despatching and how many are
> needed of each. The first issue is that there are 30-50 parts typically,
> and I don't think that 30-50 Sum(Case...) statements are the best
> solution. Secondly (and more importantly), new parts are added and removed
> on a regular basis, so hard-coding like this is pure folly.
> What are the best alternatives to this? Are there any?
> I have a way around this problem using my ASP code, but I'm interested in
> a general solution for such a scenario for future purposes as well.
> Thanks
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||There is no way to dynamically create a pivot (even in 2k5 iirc)
Unless you goto the extremes and create dynamic sql statements and use
EXEC( ... ) - which is generally very hard to maintain and has performance
impacts.
Your best bet is to return the data to the client as a two field dataset and
cross tab it in the front end.
Part Number
-- --
KSTX 1345
QUZR 45
38XJ 8723
9MSU 1437
SM5J 127
MABV 731
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OR6%23VEx$FHA.2784@.tk2msftngp13.phx.gbl...
> I want to pivot some data returned from a query. I've use the standard
> technique [Sum(Case...)] below in the past, but in this situation it's not
> ideal.
> Typical solution:
> SELECT
> SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
> ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
> ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
> ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
> ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
> ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
> ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
> ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
> ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
> ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
> ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
> ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
> FROM Sales.SalesOrderHeader
> WHERE YEAR(OrderDate) = 2002
> In my case, I'm listing the Parts that need despatching and how many are
> needed of each. The first issue is that there are 30-50 parts typically,
and
> I don't think that 30-50 Sum(Case...) statements are the best solution.
> Secondly (and more importantly), new parts are added and removed on a
> regular basis, so hard-coding like this is pure folly.
> What are the best alternatives to this? Are there any?
> I have a way around this problem using my ASP code, but I'm interested in
a
> general solution for such a scenario for future purposes as well.
> Thanks
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1134389360.359667.322330@.g14g2000cwa.googlegroups.com...
> The best solution is the one you've already cited; do it on the client
> side. Your server will love you for it :)
> However, if you must do it on the server side, google for "SQL Server
> dynamic pivot" for various solutions to this problem. Most have
> limitations, but they can do the trick. As an aside, SQL Server 2005
> has a PIVOT command built-in.
>
One thing hampering my search via google is the proliferation of help on SQL
2005!
Still... should be migrating across soon...|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1134389360.359667.322330@.g14g2000cwa.googlegroups.com...
> The best solution is the one you've already cited; do it on the client
> side. Your server will love you for it :)
> ... SQL Server 2005 has a PIVOT command built-in.
She loves me...she loves me not...:)
For dynamic xtabs and other type of pivoting problems on the server side
check out RAC @.
www.rac4sql.net

Monday, March 26, 2012

Please help

Friday, March 23, 2012

Please help

Please guide - especially about Time Dimension and approach in general

please check this not null SQL String

Wednesday, March 21, 2012

Please advise

Tuesday, March 20, 2012

Plagued by corruption

placing two matrix data regions one below the other without spacin

Monday, March 12, 2012

placing existing table on a different filegroup

Friday, March 9, 2012

pk_dtproperties Question

pk_dtproperties Question

Wednesday, March 7, 2012

Pivot-related query

Monday, February 20, 2012

Pivot Question

pivot information

Blog Archive

About Me