SQL Server (και άλλες databases)

Απ:Pivoting Tables σε SQL 2000

George J. Capnias — Wed, 27 Jul 2005 15:05:11 GMT

Επειδή πολλές διαδικασίες γινόντουσαν πολύπλοκες με το υπάρχων schema, ξανασχεδίασα όλο το τμήμα των καταλόγων από την αρχή, για να αποφύγω το πρόβλημα, και ξέθαψα την ύλη που είχα διαβάσει παλιά για τους καταλόγους.

Μόλις τελειώσω με την σχεδίαση θα σας την δείξω, ως παράδειγμα για αναφορά

George J.

Απ:Pivoting Tables σε SQL 2000

cap — Tue, 26 Jul 2005 23:49:59 GMT

Να συμπληρώσω απλά οτι θα μπορούσες να παρακάμψεις τη διαδικασία insert/update κάνοντας απευθείας insert στον temp table με κάποιον πιό έξυπνο τρόπο. Περισσότερο το έκανα για να φανεί τι ήθελα να κάνω. Το 10000 που βάζω είναι dummy τιμή, σε περίπτωση όμως που χρειαστείς πολλαπλά προϊόντα (1 row για το καθένα) ίσως έχει νόημα ως identifier.

Απ:Pivoting Tables σε SQL 2000

cap — Tue, 26 Jul 2005 14:55:05 GMT

Εεε τότε κάτσε να σου δώσω και το script του test table που χρησιμοποιησα να μην παιδεύεσαι.

CREATE TABLE [TEST] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[catalogitemid] [int] NULL ,
[catalogid] [int] NULL ,
[quantity] [int] NULL ,
[price] [decimal](10, 2) NULL ,
[startdate] [datetime] NULL ,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Απ:Pivoting Tables σε SQL 2000

George J. Capnias — Tue, 26 Jul 2005 14:52:51 GMT

Το τσεκάρω αν μου δουλέυει και σου λέω...

George J.

Απ:Pivoting Tables σε SQL 2000

cap — Tue, 26 Jul 2005 11:58:19 GMT

Και αμα σου δωσω εγώ ακόμα χειρότερη λύση τύπου "Σωτήρη"; :)

Εχω ένα τραπέζι που έχει τα πεδία σου όπως τα θέλεις. Η λύση δουλεύει μόνο για ένα προϊόν τη φορά και παράγει ένα resultset που έχει τα εξής πεδία: Ενα dummy πεδίο στην αρχή σαν pk, και μετα, q1, p1, q2, p2 κλπ οπου q=quantity, p=price

Το κοίταξα απλά να δουλεύει και δουλεύει, θέλει πολλές βελτιώσεις, είναι όμως αργά και ...βαριέμαι :) Απλά μήπως σου δώσω καμμιά ιδέα.

declare @cnt int --Holds the number of rows we will find for a specific product
declare @curr int --Current position indicator
set @curr=1

--Create a temp table with at least one field
create table #a (someid int primary key)

--Get the number of rows. No special WHERE clauses here, put your own.
select @cnt=count(quantity)
from test
where catalogid=1 and catalogitemid=1

--Sql string for quantity field
declare @field1sql varchar(8000)

--Sql string for price field
declare @field2sql varchar(8000)

--String for inserting data to the temp table
declare @insertsql varchar(8000)

set @insertsql='insert into #a values (10000,'

--Create as many columns in the temp table as rows found in the main table
while @curr<=@cnt
begin
set @field1sql='alter table #a add q' + convert(varchar(2),@curr)+' int null'
set @field2sql='alter table #a add p' + convert(varchar(2),@curr)+' decimal(10,2) null'

--Construct a dummy insert sql string
set @insertsql=@insertsql+'1,1,'

exec (@field1sql)
exec (@field2sql)

set @curr = @curr+1
end

--Now insert dummy values to the one and only row of the temp table
set @insertsql=left(@insertsql, len(@insertsql)-1) + ')'
exec (@insertsql)

declare @q int    --Quantity variable
declare @p decimal(10,2)  --Price variable
declare @cnt2 int   --Counter
declare @updatesql varchar(8000) --Update sql statement which will contain actual vals

set @cnt2=1

declare cur cursor for
select quantity, price
from test
where catalogid=1 and catalogitemid=1
open cur
fetch next from cur into @q, @p

while @@fetch_status=0
begin
set @updatesql='update #a set q' +
convert(varchar(2), @cnt2)
+ '='+convert(varchar(10), @q)
+ ',p' + convert(varchar(2), @cnt2)
+ '='+convert(varchar(10), @p)
exec (@updatesql)
fetch next from cur into @q, @p
set @cnt2=@cnt2+1

end
close cur
deallocate cur

--Yes, I know, but no quicker way!
select * from #a

drop table #a

Απ:Pivoting Tables σε SQL 2000

George J. Capnias — Tue, 26 Jul 2005 08:31:22 GMT

Η αλήθεια είναι τώρα που το βλέπω καλύτερα, σαν λύση βλέπω, τεχνικές τύπου "Σωτήρη", να το κάνω comma delimeted string ανά οριζόντια γραμμή, και μετά να το κάνω split και να το γυρνάω κατακόρυφα...

... ή να το κάνω με temp πίνακα;

George J.

Απ:Pivoting Tables σε SQL 2000

George J. Capnias — Tue, 26 Jul 2005 08:21:58 GMT

Ο πίνακας είναι της μορφής:

-itemprice_id: int identity
-itemprice_catalogitemid: int FK στον πίνακα catalogitems που είναι όλα τα προιόντα προς πώληση
-itemprice_catalodid: int FK στον πίνακα catalogs που είναι πιθανοί κατάλογοι (Χονδρική, Λιανική, κοκ)
-itemprice_quantity: int
-itemprice_price: decimal
-itemprice_startdate: datetime

Έστω τα πιθανά data:

1, 1, 1, 1, 10.00, 1/8/05
1, 1, 1, 2, 9.00, 1/8/05
1, 1, 1, 5, 8.00, 10/8/05
1, 1, 1, 10, 7.00, 10/8/05
1, 1, 1, 20, 6.00, 15/8/05
1, 1, 1, 1, 10.50, 1/7/05
1, 1, 1, 2, 9.50, 10/7/05
1, 1, 1, 5, 8.50, 15/7/05
1, 1, 1, 10, 7.50, 20/7/05
1, 1, 1, 20, 6.50, 25/7/05

Για συγκεκριμένο κατάλογο προιόντων (itemprice_catalogid) θα πρέπει να μπορώ για συγκεκριμένο προιόν (itemprice_catalogitemid) να δω τα παραπάνω δεδομένα έτσι:

itemprice_catalogitemid, 1, 2, 5, 10, 20
itemprice_catalogitemid, 10.50, 9.50, 8.50, 7.50, 0 (προσοχή δεν υπάρχει τιμή για τα 20 αν η σύγκριση της ημερομηνίας ισχύος της τιμής γίνει με το getdate())

Ναι το παραπάνω SQL είναι σωστό, αλλά δεν μπορώ να το χρησιμοποιήσω κάτι τέτοιο στην περίπτωσή μου γιατί δεν ξέρω των αριθμό των περιπτώσεων των διαφορετικών ποσοτήτων που θα υπάρχουν. Δεν θέλω ένα pivot table, αλλά να κάνω pivoting σε ένα table που δεν ξέρω τον αριθμό των γραμμών του...

Η Oracle έχει έτοιμη τέτοια συνάρτηση και ο SQL 2005. Το πρώτο μπορώ να το χρησιμοποιήσω, το δεύτερο πρέπει να περιμένω μέχρι, τουλάχιστον, τις 7 Νοεμβρίου.

George J.

Απ:Pivoting Tables σε SQL 2000

Aris — Tue, 26 Jul 2005 03:36:58 GMT

Δηλαδή, από ένα πίνακα PriceChanges της μορφής:
- ChangeDate: datetime
- ProductID: int
- NewPrice: money
- ValidForUnitsAbove: int
όπου η τιμή πώλησης είναι η πλέον πρόσφατη με την ποσότητα να πέφτει μέσα στα όρια;
Δηλαδή, κάνεις ένα
SELECT TOP 1 NewPrice FROM PriceChanges WHERE (ProductID = ? AND ? >= ValidForUnitsAbove) ORDER BY ProductID, ValidForUnitsAbove DESC, ChangeDate DESC

Ερωτήσεις:
1. Είναι σωστό το παραπάνω;
2. Οι στήλες του νέου πίνακα, ποιές είναι;

Άρης

Pivoting Tables σε SQL 2000

George J. Capnias — Mon, 25 Jul 2005 23:12:52 GMT

Ήμουν σίγουρος ότι μπορούσα να τα καταφέρω, αλλά έφαγα όλο το πρωινό μου και δεν κατάφερα να κάνω κάτι. Και από μερικά search από δω και από κει στο Internet, δεν βρήκα κάτι της προκοπής.

Ο λόγος γίνεται για ένα κατάλογο τιμών προϊόντων. Κάθε τιμή που αντιστοιχεί σε ένα προϊόν, έχει να κάνει και με τον αριθμό των κομματιών που πωλούνται και με την ημερομηνία έναρξης ισχύος της τιμής.

Εύκολο αυτό, το γράφουμε οριζόντια μέσα σε ένα πίνακα. Τώρα, το ζητούμενο είναι να το δούμε κάθετα, δηλαδή να κάνουμε τις γραμμές στήλες, ώστε να είναι πιο εύκολα τα δεδομένα στην ανάγνωση. Ο αριθμός των γραμμών δεν είναι σταθερός, μιας και κάθε προιόν έχει διαφορετικές μεταβολές στις ποσότητες που αλλάζει η τιμή του, και θέλουμε να μπορούμε να βάζουμε δίπλα από κάθε στήλη, μια δεύτερη στήλη με την προηγούμενη τιμή που ίσχυε για το προιόν.

Παλιά που δούλευα καταλόγους μέσα σε εφαρμογές, είμαι σίγουρος ότι το είχα κάνει, αλλά τώρα έχω κολλήσει ανεπανόρθωτα.

Κάποιος να βοηθήσει;

George J.