How do I prevent NULL fields in rows SQL Pivot?

0
votes

I am running a SQL Pivot against varchar.

I have the following table:

`Continent    | State      | City
--------------------------------------------- 
North America | California | Oakland
North America | California | Los Angeles
North America | California | Sacramento
North America | California | San Diego
North America | California | San Francisco
North America | California | Upton
North America | Florida    | Jacksonville
North America | Florida    | Fort Lauderdale
North America | Florida    | Miami
North America | Florida    | Tampa
North America | New York   | Albany
North America | New York   | Buffalo
North America | New York   | New York
North America | New York   | Rochester
North America | New York   | Utica
North America | Texas      | Austin
North America | Texas      | Dallas
North America | Texas      | San Antonio`

and I want to create a pivot table that looks like this:

`Continent     | California    | Florida         | New York    | Texas
-----------------------------------------------------------------------
North America | Oakland       | Jacksonville    | Albany      | Austin
North America | Los Angeles   | Fort Lauderdale | Buffalo     | Dallas
North America | Sacramento    | Miami           | New York    | San Antonio
North America | San Diego     | Tampa           | Rochester   | NULL
North America | San Francisco | NULL            | Utica       | NULL
North America | Upton         | NULL            | NULL        | NULL`

I am currently using the following code:


`        SELECT *
        FROM
        (
            SELECT Placename.Continent,
                Placename.State,
                Placename.City,
                row_number() over(partition by Placename.State order by Placename.City) rn
                FROM Placename
        ) AS SourceTable PIVOT(Min(City) FOR [State] IN([California],
        [Florida],
        [New York],
        [Texas]
        )) AS PivotTable`

Note: Min(City) and Max(City) gave me the same output

Now, after using this code, the following table is given:

`Continent     | California    | Florida         | New York    | Texas
-----------------------------------------------------------------------
North America | Oakland       | Jacksonville    | NULL        | Austin
North America | NULL          | Fort Lauderdale | Albany      | Dallas
North America | Los Angeles   | NULL            | Buffalo     | NULL
North America | Sacramento    | Miami           | New York    | San Antonio
North America | NULL          | Tampa           | NULL        | NULL
North America | San Diego     | NULL            | Rochester   | NULL
North America | San Francisco | NULL            | Utica       | NULL
North America | Upton         | NULL            | NULL        | NULL`

What is the reason that the table being output is not the same as the desired table output?

2
sqlsql-servertsqldatabase-designpivot
removing row_number now only selects the first city in each state, not all cities from each state - Blitz
you wanted all or first city of each state ? - Squirrel
all of each state - Blitz
It might be more helpful if you showed the full results of the query, especially the rn column. Your query seems to be correct, but we might learn more seeing the results. Of course if you provide the scripts necessary to populate tables with your dataset, that would help even more. Please see this article to get started: stackoverflow.com/help/how-to-ask - scratt
I just ran your exact query with your exact data, and it gave me your desired output - not your supposed actual output. So there must be more to this than posted. - Dale K

2 Answers

2
votes

just try this:

DECLARE @tbl TABLE(Continent VARCHAR(100),[State] VARCHAR(100),City VARCHAR(100));
INSERT INTO @tbl VALUES
 ('North America','California','Oakland')
,('North America','California','Los Angeles')
,('North America','California','Sacramento')
,('North America','California','San Diego')
,('North America','California','San Francisco')
,('North America','California','Upton')
,('North America','Florida','Jacksonville')
,('North America','Florida','Fort Lauderdale')
,('North America','Florida','Miami')
,('North America','Florida','Tampa')
,('North America','New York','Albany')
,('North America','New York','Buffalo')
,('North America','New York','New York')
,('North America','New York','Rochester')
,('North America','New York','Utica')
,('North America','Texas','Austin')
,('North America','Texas','Dallas')
,('North America','Texas','San Antonio');

SELECT *
FROM
(
    SELECT Placename.Continent,
        Placename.State,
        Placename.City,
        row_number() over(partition by Placename.State order by Placename.City) rn
        FROM @tbl AS PlaceName
) AS SourceTable PIVOT(Min(City) FOR [State] IN([California],
[Florida],
[New York],
[Texas]
)) AS PivotTable;

This is exactly your own query working against a mockup-table. As far as I see, the result is exactly the one you need:

+---------------+----+---------------+-----------------+-----------+-------------+
| Continent     | rn | California    | Florida         | New York  | Texas       |
+---------------+----+---------------+-----------------+-----------+-------------+
| North America | 1  | Los Angeles   | Fort Lauderdale | Albany    | Austin      |
+---------------+----+---------------+-----------------+-----------+-------------+
| North America | 2  | Oakland       | Jacksonville    | Buffalo   | Dallas      |
+---------------+----+---------------+-----------------+-----------+-------------+
| North America | 3  | Sacramento    | Miami           | New York  | San Antonio |
+---------------+----+---------------+-----------------+-----------+-------------+
| North America | 4  | San Diego     | Tampa           | Rochester | NULL        |
+---------------+----+---------------+-----------------+-----------+-------------+
| North America | 5  | San Francisco | NULL            | Utica     | NULL        |
+---------------+----+---------------+-----------------+-----------+-------------+
| North America | 6  | Upton         | NULL            | NULL      | NULL        |
+---------------+----+---------------+-----------------+-----------+-------------+

If there is more behind, please use my stand-alone sample from above, set up a running example like mine and describe what else you'd need.

0
votes

Something like this should do it for you.

--DROP TABLE #C
CREATE TABLE #C (X CHAR(1) , ID INT)
INSERT INTO #C values('A', 1)
INSERT INTO #C values(NULL, 2)
INSERT INTO #C values('B', 3)
INSERT INTO #C values('C', 4)
INSERT INTO #C values(NULL, 5)
INSERT INTO #C values('D', 6)

select *
from #C

enter image description here

SELECT ID,CASE WHEN X is not null
            THEN X
            ELSE (SELECT max(X)
                  FROM #C
                  WHERE ID <= t.ID)
       END AS X
FROM #C t

enter image description here

If its sql 2012 use this

SELECT ID,
COALESCE(Name,
LAG(Name,1) OVER (ORDER BY ID )) AS Name
FROM Table
If there can be multiple gaps use this instead
SELECT ID,
COALESCE(Name,
MAX(COALESCE(Name,'')) OVER (ORDER BY ID  ROWS BETWEEN UNBOUNDED PRECEDING AND  1 PRECEDING)) AS Name
FROM Table