Stack Exchange
Pivot and filter
I am trying to create a dynamic Pivot, however cant seem to get it working. 
Table:
SELECT [CaseID], [ClientID]
from [RegData]

Currently Returns:
CaseID--|ClientID| RecID
------------------------
7845107 |115172  |410
7845107 |164570  |1197
7845107 |115655  |416
7845107 |154244  |425
7856846 |116684  |151
7856846 |112354  |445
7455444 |115172  |492
7455444 |164570  |518
7455444 |115655  |1297
7455444 |154244  |681

I need it to return like:
CaseID--|Val1----|Val2----|Val3----|Val4----|
7845107 |115172  |164570  |115655  |154244  |
7856846 |116684  |112354  |        |        |
7455444 |115172  |164570  |115655  |154244  |

Then, I need to find the instances where CaseID is different, but VAL1, VAL2, VAL3 etc are the same.
CaseID--|Val1----|Val2----|Val3----|Val4----|
7845107 |115172  |164570  |115655  |154244  |
7455444 |115172  |164570  |115655  |154244  |


How do you suppose to dictate that 164570 is Val2 and 115655 is Val3? SQL Server needs some way supported by the data (or something deterministic that you can introduce to the query) to support that ordering. The query you have here has no ORDER BY, so I suspect you assume that because that's the way the data was returned this time, that's the order it will always be returned? This is a common misconception, but it's a misconception nonetheless. Do you have another column that can help dictate your expected ordering (such as an identity or date/time column)?

Hi Aaron, very fair point. To minimize clutter, I omitted the 'RecID' column which is actually the unique identifier for each row.

So are those rows ordered in your example above ordered by RecID? Typically we prefer full information... We're smart folks and clutter usually doesn't slow us down too much. So I suggest you add the RecID values at least to the first set.

Their is no order by in the first table. I guess the column headers could be the ClientID if required. That is, in the example returned, the column header of Value1 might be '115172', and all rows with '115172' will have a value '115172'. Not sure how you can then find the instances of where CaseID is different, and the column headers are the same...

I know there is no ORDER BY in the first table. That's the problem. I asked you to provide the corresponding RecID values. Without those, I can't really know if there is a guaranteed way to provide the result you're looking for. Could you please provide the RecID values I've asked for multiple times, even if you think they're irrelevant clutter? I'm not asking for my health, I'm asking so I can help you solve your issue.

Ok. So again, neither RecID nor ClientID are dictating the order. The order you are seeing is arbitrary (since you have no order by) and it may change completely the next time you run the query (the order SQL chooses when you've told the engine you don't care can change based on many factors). You want to develop this pivot query that's based on matching values that you're currently seeing in these arbitrary results, and SQL Server just doesn't work that way. Unless you can explain a logical reason why both instances of 154244 belong under the Value4 column, I'm afraid you're out of luck...

...you might get someone to provide an example that dumps your output into a #temp table with an identity column, but those identity values will be based on the same arbitrary ordering and will be subject to the same "it works today, but it might not tomorrow" disclaimer that you may or may not get (or believe).

A lot of disclaimers here. Relies on you only caring about cases where all 4 values matter. I did not test what would happen if one of the cases had a 5th client that doesn't match, or if there are more than two cases with the same set of clients. It works for the sample data you provided except that the values you expect to see in each of the Value1 / Value2 columns etc. are presented in a different order than your sample output.
DECLARE @x TABLE(CaseID INT, ClientID INT);

INSERT @x SELECT 7845107,115172 UNION ALL SELECT 7845107,164570
UNION ALL SELECT 7845107,115655 UNION ALL SELECT 7845107,154244
UNION ALL SELECT 7856846,116684 UNION ALL SELECT 7856846,112354
UNION ALL SELECT 7455444,115172 UNION ALL SELECT 7455444,164570
UNION ALL SELECT 7455444,115655 UNION ALL SELECT 7455444,154244;

;WITH x AS
(
  SELECT CaseID, ClientID, rn = ROW_NUMBER() OVER 
  (PARTITION BY CaseID ORDER BY ClientID) 
  FROM @x
),
y AS 
(
    SELECT x.CaseID, x.ClientID, x.rn
    FROM x INNER JOIN x AS x2
    ON x.CaseID <> x2.CaseID
    AND x.ClientID = x2.ClientID
    AND x.rn = x2.rn
)
SELECT CaseID, 
    Value1 = MAX(CASE WHEN rn = 1 THEN ClientID END),
    Value2 = MAX(CASE WHEN rn = 2 THEN ClientID END),
    Value3 = MAX(CASE WHEN rn = 3 THEN ClientID END),
    Value4 = MAX(CASE WHEN rn = 4 THEN ClientID END)
FROM y
GROUP BY CaseID;

Results:
CaseID   Value1  Value2  Value3  Value4
-------  ------  ------  ------  ------
7455444  115172  115655  154244  164570
7845107  115172  115655  154244  164570


Doesnt seem to compile. Has a problem with , after INSERT @x VALUES
(7845107,115172),    (7845107,164570),

@Rya INSERT each line sperately then by doing INSERT INTO @x (val1, val2) and repeat the INSERT INTO for each value pair. Seems sql 2005 can't insert multiple values at once if you get an error.

Sorry @Rya, I missed the SQL Server 2005 tag (that syntax is valid on modern versions of SQL Server). Of course you could have also tried just the query against your table instead of against @x - I only included that to demonstrate that it works kind of like what you were asking for.

This is what I came up with.  Not very efficient, but it works... 
I created a table. I happen to know that their were no more than 9 cases of the same in the set, hence Val9.
DECLARE 
    @totalRecords int,
    @counter int,
    @QueryCaseID int,
    @QueryClientID int,
    @queryVal1 int,
    @queryVal2 int,
    @queryVal3 int,
    @queryVal4 int,
    @queryVal5 int,
    @queryVal6 int,
    @queryVal7 int,
    @queryVal8 int,
    @queryVal9 int

Set @totalRecords = (Select count(*) from RegData) +1 
Set @counter = 1

-- Get the rows in the table and put them into the 'RegData_reformat' table
While @counter < @totalRecords
    Begin

        -- get the CASEID to cross query to reformatted table
        SET @QueryCaseID = (Select CaseID from RegData where RecID = @counter)

        -- get the ClientId to cross query to reformatted table
        SET @QueryClientID = (Select ClientID from RegData where RecID = @counter)



        -- assign the columns to variables
        SET @queryVal1 = (Select Val1 from RegData_reformat where CaseID = @QueryCaseID)
        SET @queryVal2 = (Select Val2 from RegData_reformat where CaseID = @QueryCaseID)
        SET @queryVal3 = (Select Val3 from RegData_reformat where CaseID = @QueryCaseID)
        SET @queryVal4 = (Select Val4 from RegData_reformat where CaseID = @QueryCaseID)
        SET @queryVal5 = (Select Val5 from RegData_reformat where CaseID = @QueryCaseID)
        SET @queryVal6 = (Select Val6 from RegData_reformat where CaseID = @QueryCaseID)
        SET @queryVal7 = (Select Val7 from RegData_reformat where CaseID = @QueryCaseID)
        SET @queryVal8 = (Select Val8 from RegData_reformat where CaseID = @QueryCaseID)
        SET @queryVal9 = (Select Val9 from RegData_reformat where CaseID = @QueryCaseID)


        --determine which column to insert the data into


--  Insert the data into the formatted table

        IF @queryVal1 IS NULL
            BEGIN
                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val1] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END

    -- Column 2 test
        else IF @queryVal2 IS NULL
            BEGIN
                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val2] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END

    -- Column 3 test
        else IF @queryVal3 IS NULL
            BEGIN
                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val3] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END

    -- Column 4 test
        else IF @queryVal4 IS NULL
            BEGIN
                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val4] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END

    -- Column 5 test            
        else IF @queryVal5 IS NULL
            BEGIN

                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val5] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END

    -- Column 6 test
        else IF @queryVal6 IS NULL
            BEGIN

                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val6] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END

    -- Column 7 test
        else IF @queryVal7 IS NULL
            BEGIN

                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val7] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END

    -- Column 8 test
        else IF @queryVal8 IS NULL
            BEGIN

                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val8] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END

    -- Column 9 test
        else IF @queryVal9 IS NULL
            BEGIN

                UPDATE [4DISCSReporting].[dbo].[RegData_reformat]
                SET [Val9] = @QueryClientID
                WHERE CaseID = @QueryCaseID
            END



        ELSE
            BEGIN
                print 'Ran out of columns to enter data into'
                Select 'FATAL ERROR: ran out of columns to enter data into' as Error
            END



    set @counter = @counter +1  


    END

