How do I prevent NULL fields in rows SQL Pivot?

Question

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?

Answer 1

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

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 

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

Answer 2

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.