Argthtjtr

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?

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.

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