Prev: Nested Set Model - finding the level 1 parent node of a child node
Next: bit datatype displays as 'true'
From: Wes Groleau on 26 Jul 2010 13:30
A table lists multiple events where each event has four attributes
(columns), call them A, B, C, P

It would be simple to pivot to get a table with columns for
A, B, C, P=1, P=2, P=3, etc.

However, I need the columns to be A, B, C, P<1, P<2, P<3, etc.

In other words, if a row of the "simple" way were X, Y, Z, 7, 3, 5, 2
then what I actually need is X, Y, Z, 7, 10, 15, 17 because any P less
than N is also less than N+1.

I know I can compute the values (X, Y, Z, 7, 10, 15, 17), load them into
a temporary table, and pivot that, but maybe there's something simple
that my SQL talents don't recognize immediately?

If it matters, the result will end up in SSRS.

--
Wes Groleau

Learning to see the forest instead of the trees.
http://Ideas.Lang-Learn.us/WWW?itemid=75
From: --CELKO-- on 26 Jul 2010 14:06
>> A table lists multiple events where each event has four attributes <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

Here is my guess:

CREATE TABLE Foobar
(a CHAR(1) NOT NULL,
b CHAR(1) NOT NULL,
c CHAR(1) NOT NULL,
p INTEGER NOT NULL,
PRIMARY KEY (??)); -- make a wild guess!!

I think that an event should have a date or time. But you did not give
us one, so this is not going to be a table at all!!

>> It would be simple to pivot to get a table with columns for A, B, C, P=1, P=2, P=3, etc. <<

After reading this vague narrative for a few minutes, I gather that
your values of p mean the PHYSICAL position of a row relative to a
grouping in a non-table. In short, this is a sequential file problem
and not RDBMS at all.

>> However, I need the columns to be A, B, C, P<1, P<2, P<3, etc. In other words, if a row of the "simple" way were X, Y, Z, 7, 3, 5, 2 then what I actually need is X, Y, Z, 7, 10, 15, 17 because any P less than N is also less than N+1. <<

Another guess: You are trying to get a running total of p values and
have assumed that a table is a sequential file.

INSERT INTO Foobar (a, b, c, p)
VALUES ('X', 'Y', 'Z', 7),
('X', 'Y', 'Z', 3),
('X', 'Y', 'Z', 5),
('X', 'Y', 'Z', 2);

In ANSI SQL and other products, you can write a running total with
this simple syntax. But not in SQL Server:

SELECT a, b, c,
SUM (p)
OVER (PARTITION BY a,b,c
ORDER BY ?? -- missing event date?
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Foobar
GROUP BY a, b, c;

0ou can do this in SQL Server, but it is slow and ugly for large data
sets. Is this what you wanted? Will you try again with DDL and clear
specs?
From: Erland Sommarskog on 26 Jul 2010 16:13
Wes Groleau (Groleau+news(a)FreeShell.org) writes:
> A table lists multiple events where each event has four attributes
> (columns), call them A, B, C, P
>
> It would be simple to pivot to get a table with columns for
> A, B, C, P=1, P=2, P=3, etc.
>
> However, I need the columns to be A, B, C, P<1, P<2, P<3, etc.
>
> In other words, if a row of the "simple" way were X, Y, Z, 7, 3, 5, 2
> then what I actually need is X, Y, Z, 7, 10, 15, 17 because any P less
> than N is also less than N+1.
>
> I know I can compute the values (X, Y, Z, 7, 10, 15, 17), load them into
> a temporary table, and pivot that, but maybe there's something simple
> that my SQL talents don't recognize immediately?
>
> If it matters, the result will end up in SSRS.

It certainly had help if you had posted a CREATE TABLE statement for your
table and some sample data. But if I am to guess, this works:

SELECT event, A, B, C,
[P < 1] = SUM(CASE WHEN P < 1 THEN 1 ELSE 0 END),
[P < 2] = SUM(CASE WHEN P < 2 THEN 1 ELSE 0 END),
[P < 3] = SUM(CASE WHEN P < 3 THEN 1 ELSE 0 END)
FROM tbl
GROUP BY event, A, B, C

If this is flat wrong, please read my response from the beginning and act
accordingly.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Plamen Ratchev on 26 Jul 2010 18:20
This is a request to pivot running total. Joe has the right query, but
does not to work in SQL Server yet... It is possible to do it in SQL
Server with subquery but the key column(s) for ordering the table
needs to be defined.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Wes Groleau on 26 Jul 2010 19:30
On 07-26-2010 18:20, Plamen Ratchev wrote:
> This is a request to pivot running total. Joe has the right query, but
> does not to work in SQL Server yet... It is possible to do it in SQL
> Server with subquery but the key column(s) for ordering the table
> needs to be defined.

No, Joe did not. Erland and someone on another forum showed
that people are not computers and can often figure out what
someone means even if that person fails to write a book
on the subject.

--
Wes Groleau

A provocative quote
http://Ideas.Lang-Learn.us/WWW?itemid=87
 |  Next  |  Last
Pages: 1 2 3
Prev: Nested Set Model - finding the level 1 parent node of a child node
Next: bit datatype displays as 'true'