Create View SQL

[MichelleD]

I am using New > View to try to create a database view in SQLExpress.  I want to pivot the table like it works in this Access query:

TRANSFORM First(Leaf.last_edi_1) AS FirstOflast_edi_1

SELECT Leaf.RouteId

FROM Leaf

GROUP BY Leaf.RouteId

PIVOT Leaf.Cycle;

I have tried making something similar work in with SQL to create a database view with no luck.  I can get the RouteId as rows, but can't figure out how to make the 10 cycles show up as columns with the last_edited_date filled in - it always errors on Pivot.  Can I not use Pivot in Esri and/or SQLExpress?  If not, is there a work around to transpose the table with Create View?

[ChristianWells · Accepted solution]

Hi Michelle,

Here is a small sample I put together with your outline:

--Create Leaf table
create table leaf (ROUTEID INT, CYCLE VARCHAR(10), LAST_EDIT DATE);

--Insert records
INSERT INTO LEAF VALUES (1, 'CYCLE1', '10/23/2015');
INSERT INTO LEAF VALUES (1, 'CYCLE2', '1/23/2015');
INSERT INTO LEAF VALUES (1, 'CYCLE3', '3/23/2015');
INSERT INTO LEAF VALUES (2, 'CYCLE1', '5/23/2015');
INSERT INTO LEAF VALUES (2, 'CYCLE2', '12/23/2015');
INSERT INTO LEAF VALUES (2, 'CYCLE3', '7/23/2015');

--Create the select statement with a pivot
SELECT ROUTEID, CYCLE1, CYCLE2, CYCLE3 FROM 
(SELECT ROUTEID, CYCLE, LAST_EDIT FROM LEAF) LEAF_PIV 
PIVOT (MAX(LAST_EDIT) FOR CYCLE IN (CYCLE1, CYCLE2, CYCLE3)) PIV

Results:

In addition, this query does work for me in ArcMap/ArcCatalog if I cast the dates to varchar. What field types do you have for your last_edit field?

[MichelleD]

Christian,

Thanks so much for your reply.  I still cannot get PIVOT to work, but it must be because I am not getting the SQL statement right and/or that my date field is a date.  I ended up getting the results I wanted by first creating one view for each cycle:

SELECT RouteId AS Route1, last_edited_date AS Cycle1

FROM View_Test

WHERE Cycle=1 AND Status=5

GROUP BY RouteId, last_edited_date

Then I joined them together in a single view:

SELECT C1.Route1, C1.Cycle1, C2.Cycle2, C3.Cycle3

FROM (C1 LEFT JOIN C2 ON C1.Route1 = C2.Route2)

LEFT JOIN C3 ON C1.Route1 = C3.Route3

It works, but I am sure there is a more elegant way to do this in a single view for someone more proficient in SQL.

[ChristianWells]

Hi Michelle,

Does the sample I provided work for you in SQL Server Management Studio?

If you want to share your data as a csv or dbf file, I can take a look at it.

Christian

[MichelleD]

Christian,

I am joining the View to my Routes layer with the cycle dates as columns so I can show a list of all the dates each cycle was completed (Status = 5) for a route on a single pop-up in a web map. Everything works with the three test cycles I set up as described (one view for each cycle where Status=5 joined into a single view), and I am hoping performance isn’t impacted when I add in the remaining seven cycles. I have attached a dbf if you think there is a better way to set up the View and want to take a look at it. Otherwise I will use my 10 + 1 view solution, in which case I will mark your first response as the correct answer since it worked for you (I have only been working in ArcCatalog and may not have gotten that PIVOT query just right, especially adding in the status condition).

Michelle