0

I have this simple query:

enter image description here

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 Frotas.Viaturas_Temp v
            on u.unidade = v.unidade_id
                where v.parametrizado=0
            ORDER BY 1
            For XML PATH ('')
        ), 2, 1000) [Unidades]
From Unidades.Unidades u

Conversion failed when converting the varchar value 'Superintendência de Polícia Técnico-Científica' to data type int.

4

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.

1

made it, with something simple ( i'm really bad at programming :

Select main.unidade_id,
       Left(Main.viaturas,Len(Main.viaturas)-1) As "Viaturas"
       into #temp
From
    (
        Select distinct v2.unidade_id, 
            (
                Select v1.placa + ',' AS [text()]
                From frotas.Viaturas_Temp v1
                Where v1.unidade_id = v2.unidade_id
                ORDER BY v1.unidade_id
                For XML PATH ('')
            ) [viaturas]
        From frotas.Viaturas_Temp v2
) [Main]


select u.unidade, t.viaturas
from Unidades.Unidades u
    join #temp t
        on u.id=t.unidade_id
    drop table #temp

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.