How to PIVOT/UNPIVOT if I don't know how many columns will it have? I have this simple query:

I want to list something like this:


  Unidade1 \ PLaca1 \ Placa2 \ Placa3
  
  Unidade2 \ Placa1 \ Placa2


But I don't know how many placas a unidade will have.

I was trying to pivot like this:

SELECT   [undiade], [placa1],   [placa2], ..
FROM   Table1
join table2
bla bla bla 
PIVOT
(
       Unidade
       FOR [unidade] IN ([placa1], [placa2], [placa3])
) AS P


if there's something I can't understand is how to pivot.



EDIT1:

I tried this with no succes:

Select distinct Unidade, 
    substring(
        (
            Select ','+unidade  AS [text()]
            From unidades.Unidades u
            join t1.Viat v
            on u.unidade = v.unidade_id
                where v.parametrizado=0
            ORDER BY 1
            For XML PATH ('')
        ), 2, 1000) [Unidades]
From unid.unids.unids u


Conversion failed when converting the varchar value 'field' to data type int.
As Julien mentioned, dyanamic sql will be your answer for this. Below is an example of using pivoting with an EAV system. It really helps with reading the data.

DECLARE @Cols NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)
DECLARE @ClassName NVARCHAR(50)
SET @ClassName = 'customer'

SELECT @Cols = STUFF((SELECT distinct ',' + QUOTENAME(property_name) 
                                FROM Entity_Property ep
                                INNER JOIN Entity e ON ep.eid = e.eid
                                INNER JOIN Class c ON c.cid = e.cid
                                WHERE c.class_name = @ClassName                   
                              FOR XML PATH(''), TYPE
                              ).value('.', 'NVARCHAR(MAX)') 
                              ,1,1,'')

SET @Query = 'SELECT *
FROM 
      (SELECT ep.eid, ep.property_name, ep.property_val FROM Entity_Property ep INNER JOIN Entity e ON e.eid = ep.eid INNER JOIN Class c ON c.cid = e.CID WHERE c.class_name = ''' + @ClassName + ''') as src      
PIVOT
(
      MAX(Property_val)
      FOR property_name IN (' + @Cols + ')
) AS piv;'

EXEC(@Query)


- EDIT -

Here is the code that will do what you are looking for. I used a temp table as my source of information instead of the query you gave, so feel free to use that instead of #Table. The code below can be run stand alone to see what the output would look like, so with some tweaking, you can tie it to your tables.

-- Mimicing what we have for testing purposes
CREATE TABLE #Table (
    Unidade VARCHAR(100),
    Placa CHAR(8)
)

INSERT INTO #Table (Unidade, Placa) VALUES
('Divisao Administrativa', 'DJL-2937'),
('Instituto de Crimalistica', 'DJL-2690'),
('Instituto de Crimalistica', 'CDV-7883'),
('Instituto de Crimalistica', 'DJP-2304'),
('Instituto Medico Legal', 'DJL-2925'),
('Instituto Medico Legal', 'DJL-2689'),
('Instituto Medico Legal', 'DJL-2851'),
('Instituto Medico Legal', 'CMW-6316')

-- Using a temp table to hold a copy of our data with a new column for column headers
SELECT 
        Unidade,
        'Placa' + CAST(ROW_NUMBER() OVER (PARTITION BY Unidade ORDER BY Placa) AS VARCHAR(10)) AS NewColumnHeader, -- If you have an order for numbering you want to use, add it to the above query and use it here
        Placa
INTO #TableWithNewColumnHeaders -- The 'dirty' way of creating a temp table.
FROM #Table

-- Now grab what all our new columns will be (Placa1, Placa2, etc)
DECLARE @Cols NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)

SELECT @Cols = STUFF((SELECT distinct ',' + QUOTENAME(NewColumnHeader) 
                           FROM #TableWithNewColumnHeaders                                
                           FOR XML PATH(''), TYPE
                           ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

-- Now we pivot our results
SET @Query = 'SELECT p.*
        FROM 
       (SELECT Unidade, NewColumnHeader, Placa
        FROM #TableWithNewColumnHeaders)     AS SourceQuery     
PIVOT
(
      MAX(Placa)
      FOR NewColumnHeader IN (' + @Cols + ')
) AS p'

EXEC(@Query)


The idea is, we create a new column that will become our new column header. This is what lets us get the column names we want. Once we do that, it's a simple pivot statement to turn the header values into our new column names.