Δύσκολο sql query

[dvm]

Καλημέρα παιδιά.
Θέλω την βοήθειά σας στο ακόλουθο πρόβλημα. Έχω έναν πίνακα πχ PRODUCT, με κάποιες βασικές πληροφορίες για ένα προϊόν πχ. Όμως τα περισσότερα δεδομένα βρίσκονται σε άλλο πίνακα ας τον πούμε DATA. Θέλω να φτιάξω ένα sql query που να μου φέρει αυτά τα DATA, αλλά το πρόβλημά μου είναι τα πολλά joins και πως να τα χειριστώ.

Πχ

PRODUCT
======
ID,NAME, DATE_CREATED
1,  PLASMA TV, 11/6/2009
DATA
====
ID, F_PRODUCT, TYPE, VALUE
1, 1, SCREEN_SIZE, 42"
2, 1, SCREEN_TYPE, PLASMA
3, 1, PRICE, 2.000
4, 1, MAKER, PSONI

Από τα παραπάνω θέλω να πάρω:

ID, NAME, SCREEN_SIZE, SCREEN_TYPE, PRICE, MAKER
1   PLASMA TV, 42", PLASMA, 2.000, PSONI

Τα TYPES θα τα ξέρω και θα είναι προκαθορισμένα, δεν θέλω να πάρω δυναμικά το column name, απλά θέλω να φτιάξω τα joins.

Ευχαριστώ.

[antoine]

Τι βάση δεδομένων χρησιμοποιείς;

[dvm]

Oracle, αλλά δεν ξέρω πολλά specific πάνω της. Την εγκατάσταση την κάνουν οι DBA. Απλά είναι δύστροποι και δεν θέλω να τους ζητήσω το query.

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

[antoine]

Λοιπόν, τα παρακάτω είναι για SQL Server.

Οι functions είδα υπάρχουν και στην Oracle, απλά δεν έχω κάπου να τα δοκιμάσω για να σου πω σίγουρα. Πάνω κάτω πάντως κάπως έτσι θα είναι.

Σου παραθέτω και τα tables που έφτιαξα για να το δοκιμάσεις στην Oracle

CREATE TABLE #Product (ProductId INT, [Name] VARCHAR(50), Created DATETIME)
INSERT INTO #Product
SELECT 1, 'PLASMA TV', '2009-06-11'

CREATE TABLE #ProductDetails (DetailsId INT, ProductId INT, Type VARCHAR(50), Value VARCHAR(100))
INSERT INTO #ProductDetails
SELECT 1, 1, 'SCREEN_SIZE', '42``'
UNION SELECT 2, 1, 'SCREEN_TYPE', 'PLASMA'
UNION SELECT 3, 1, 'PRICE', '2.000'
UNION SELECT 4, 1, 'MAKER', 'PSONI'

Αν ξέρεις τις στήλες που θες να δείξεις τότε μπορείς εύκολα να γράψεις το παρακάτω.

SELECT *
FROM 
(
	SELECT [ProductId], [Type], [Value]
	FROM #ProductDetails
) P
PIVOT
( 
	MAX([Value]) FOR [Type] IN ([MAKER], [PRICE], [SCREEN_SIZE], [SCREEN_TYPE])
) AS PVT
JOIN #Product S ON PVT.[ProductId] = S.[ProductId]

Αν θες κάτι πιο δυναμικό, και δεν ξέρεις τα properties του κάθε αντικειμένου τότε θα κάνεις το εξής:

DECLARE @SQL NVARCHAR(4000),
        @COLS NVARCHAR(4000)
 
SELECT @COLS = COALESCE(@COLS + ',[' + [Type] + ']','[' + [Type] + ']')
FROM #ProductDetails
GROUP BY [Type]

SET @SQL = N'SELECT * ' + CHAR(13)
SET @SQL = @SQL + 'FROM	( ' + CHAR(13)
SET @SQL = @SQL + '		SELECT [ProductId], [Type], [Value] ' + CHAR(13)
SET @SQL = @SQL + '		FROM #ProductDetails ' + CHAR(13)
SET @SQL = @SQL + ') P PIVOT (MAX([Value]) FOR [Type] IN (' + @COLS + ')) AS PVT' + CHAR(13)
SET @SQL = @SQL + 'JOIN #Product S ON PVT.[ProductId] = S.[ProductId]' + CHAR(13)


EXEC SP_EXECUTESQL @SQL

Και τα δύο φέρνουν τα ίδια αποτελέσματα.

Σε SQL Server 2005 αυτά παίζουν που τα δοκίμασα τώρα αλλά σε Oracle (υπάρχουν οι functions) μπορεί να χρειάζονται κάποια μικροαλλαγή στη σύνταξη. Κάπου εκεί παίζεις πάντως.


εδιτ(1): Εννοείται όπου χρειάζεσαι κολλάς και το where statement που επιθυμείς.
εδιτ(2): Ψάξε στο google για Oracle PIVOT και δες κάποια παραδείγματα.

[dvm]

Μέχρι να δω την απάντησή σου το έκανα με τον τρόπο που βρήκα. Για 2 ενώσεις (δύο φορές το DATA) δουλεύει. Του βάζω όλα τα πεδία όμως (49!) και δεν μου επιστρέφει κάτι (εκτός του ότι την 1η φορά είναι αρκετά αργό, όταν το ξαναεκτελώ γίνεται αμέσως). Υπάρχει περίπτωση να έχει περιορισμό στα πόσα joins μπορώ να κάνω;

Θα κοιτάξω και την pivot που λες, αλλά δεν την έχω ξαναδεί και θα μου πάρει κάποια ώρα. Σ'ευχαριστώ πάντως για την αναλυτικότατη απάντηση.

Να και πως έχω φτιάξει το μικρό query που δουλεύει:

select 
    P.ID                    "Product ID",
    D1.VALUE                "Maker",
    D2.VALUE                "Price"
from
    PRODUCT P,
    DATA D1,
    DATA D2
where
    P.TYPE  = 'TV'          and
    D1.TYPE = 'MAKER'       and
    D2.TYPE = 'PRICE'       and    
    P.ID = D1.F_PRODUCT     and
    P.ID = D2.F_PRODUCT
order by 
    "Product ID";

Το P.TYPE το πρόσθεσα ώστε να μου φέρει για παράδειγμα όλα τα προϊόντα που είναι τηλεοράσεις.

[antoine]

Το γεγονός ότι την δεύτερη φορά είναι πιο γρήγορο οφείλεται στο ότι έχει κρατήσει η Oracle το execution plan για το query σου και δεν χρειάζεται να το υπολογίσει πάλι.

Αυτό που ρωτάς πάντως pivot / cross tab query. Καλύτερα να το ψάξεις έτσι παρά να φτιάξεις κάτι με selfjoins το οποίο θα καθυστερεί αισθητά και στην τελική να μάθεις και κάτι παραπάνω που θα σου μείνει παρά να το κάνεις μπακάλικα και με το λάθος τρόπο.

Αν έχεις 49 στήλες δοκίμασε το 2ο query που σου παρέθεσα το οποίο διαβάζει από τον detail πίνακά σου όλα τα properties του προϊόντος και τα μετατρέπει σε στήλες. Δεν θα είναι δύσκολο να το μετασχηματίσεις σε oracle.