Pivoting without NULL 2

[rivi10]

I was able to flatten a table (made rows into columns). However my output has NULLS. I want to have just one row for every document name.

I do not want to create Procedure or use a Cursor. I want to know can this still be done with a query.

Thanks for your help!

Table_1
ID(PK) DOC_ID DOC_VERSION NAME
Y1 ABC1234 2 DOC1
Y2 DEF5678 2 DOC1
Y3 ABC1234 1 DOC1
Y4 DEF5678 1 DOC1
Y5 ABC1234 3 DOC2
Y6 DEF5678 3 DOC2
Y7 ABC1234 2 DOC2
Y8 DEF5678 2 DOC2
Y9 ABC1234 1 DOC2
Y10 DEF5678 1 DOC2
Y11 ABC1234 1 DOC3
Y12 DEF5678 1 DOC3

Table_3
colID (PK)) VERSIONID PROPERTYID PTY
X1 ABC1234 PTYID1 Doc Code
X2 DEF5678 PTYID2 Doc Type

Table_2
ID NAME
PTYID1 doccode1
PTYID2
PTYID1 doccode2
PTYID2 pdfdocument
PTYID1 doccode3
PTYID2 pptdocument

SELECT MAX(TABLE_1.DOC_VERSION) as MAXV, TABLE_1.NAME AS DOCUMENT_NAME, CASE WHEN TABLE_2.NAME='Doc Code' THEN TABLE_3.PTY END DOCCODE, CASE WHEN TABLE_2.NAME='Doc Type' THEN TABLE_3.PTY END DOCTYPE,

FROM TABLE_3 INNER JOIN TABLE_1
ON TABLE_3.VERSIONID = TABLE_1.DOC_ID
INNER JOIN TABLE_2
ON TABLE_3.PROPERTYID = TABLE_2.ID

GROUP BY TABLE_1.NAME AS DOCUMENT_NAME,
CASE WHEN TABLE_2.NAME='Doc Code' THEN TABLE_3.PTY END,
CASE WHEN TABLE_2.NAME='Doc Type' THEN TABLE_3.PTY END

Output:
MAXV DOCUMENT_NAME DOCCODE DOCTYPE
2 DOC1 doccode1 NULL
2 DOC1 NULL
3 DOC2 doccode2 NULL
3 DOC2 NULL pdfdocument
1 DOC3 doccode3 NULL
1 DOC3 NULL pptdocument


Expected Output:

MAXV DOCUMENT_NAME DOCCODE DOCTYPE
2 DOC1 doccode1
3 DOC2 doccode2 pdfdocument
1 DOC3 doccode3 pptdocument

 

[gmmastros]

SELECT MAX(TABLE_1.DOC_VERSION) as MAXV, 
       TABLE_1.NAME AS DOCUMENT_NAME, 
       Max(CASE WHEN TABLE_2.NAME='Doc Code' THEN TABLE_3.PTY END) DOCCODE, 
       Max(CASE WHEN TABLE_2.NAME='Doc Type' THEN TABLE_3.PTY END) DOCTYPE
FROM  TABLE_3 INNER JOIN TABLE_1 
ON TABLE_3.VERSIONID = TABLE_1.DOC_ID 
    INNER JOIN TABLE_2 
ON TABLE_3.PROPERTYID = TABLE_2.ID

GROUP BY   TABLE_1.NAME AS DOCUMENT_NAME

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rivi10]

I have tried that and the output is not correct.

MAXV DOCUMENT_NAME DOCCODE DOCTYPE
2 DOC1 doccode3 pptdocument
3 DOC2 doccode3 pptdocument
1 DOC3 doccode3 pptdocument

 

[SimonSellick]

I can't see how the information in Table_3 is related to the other data. Is there a column missing?

 

[SimonSellick]

Sorry - I meant Table_2.

 

[rivi10]

Please ignore my above comment.

Thanks to GM it works like a charm!

However, I have seen one of your posts regarding pivots and I noticed that you have used MIN and gave an explanation that it would ignore NULLs.

In my case, I tried using MIN and it worked the same.

Is there any particular way I need to choose.

Thanks for the fast reply.

Rivi

 

[rivi10]

SimonSellick,

I got my answer but just for information.

I kind of screwed up my tables, it is actually like this.

Table_1
ID DOC_ID DOC_VERSION NAME
Y1 ABC1234 2 DOC1
Y11 POI2322 1 DOC3
Y3 ABC1234 1 DOC1
Y5 EFGH904 3 DOC2
Y7 EFGH904 2 DOC2
Y9 EFGH904 1 DOC2

Table_4
COLID VERSIONID PROPERTYID NAME
X1 ABC1234 PTYID1 doccode1
X2 ABC1234 PTYID2 NULL
X3 EFGH904 PTYID1 doccode2
X4 EFGH904 PTYID2 pdfdocument
X5 POI2322 PTYID1 doccode3
X6 POI2322 PTYID2 pptdocument

Table_2
PID ID PTY
1 PTYID1 DocCode
2 PTYID2 DocType

My original query was:

SELECT MAX(Table_1.DOC_VERSION) as MAXV, Table_1.NAME AS DOCUMENT_NAME,
CASE WHEN Table_2.PTY='DocCode' THEN Table_4.NAME END DOCCODE,
CASE WHEN Table_2.PTY='DocType' THEN Table_4.NAME END DOCTYPE

FROM Table_4 INNER JOIN Table_1
ON Table_4.VERSIONID = Table_1.DOC_ID
INNER JOIN Table_2
ON Table_4.PROPERTYID = Table_2.ID

GROUP BY Table_1.NAME,
CASE WHEN Table_2.PTY='DocCode' THEN Table_4.NAME END,
CASE WHEN Table_2.PTY='DocType' THEN Table_4.NAME END

 

[gmmastros]

Min, Max, it doesn't really matter. The important thing here is ignoring NULLS. It could matter under the correct circumstances, but not this one.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom