Wie Sie pivot-text-Spalten in SQL Server?

Ich neu erstellt, diese in sql server, und es funktioniert Prima.

Ich versuche zu konvertieren, das zu arbeiten, wenn man nicht weiß, was der Inhalt sein wird, in der ART und BESCHREIBUNG Spalten.

War ich auch mit diesem als Leitfaden. (Konvertieren von Zeilen in Spalten mit "Drehpunkt" in SQL Server)

BEARBEITEN ----

Hier ist meine Lösung für die oben, wo Sie NICHT WISSEN, den Inhalt in einem der Felder....

-- setup commands
        drop table #mytemp
        go

        create table #mytemp (
            id varchar(10),
            Metal_01 varchar(30),
            Metal_02 varchar(100)
        )


-- insert the data
        insert into #mytemp
        select 'C-0','Metal One','Metal_One' union all
        select 'C-0','Metal & Two','Metal_Two' union all
        select 'C-1','Metal One','Metal_One' union all
        select 'C-1','Metal (Four)','Metal_Four' union all
        select 'C-2','Metal (Four)','Metal_Four' union all
        select 'C-2','Metal /Six','Metal_Six' union all
        select 'C-3','Metal Seven','Metal_Seven' union all
        select 'C-3','Metal Eight','Metal_Eight' 

-- prepare the data for rotating:
        drop table #mytemp_ReadyForRotate
        select *,
                    replace(
                        replace(
                            replace(
                                replace(
                                    replace(
                                                mt.Metal_01,space(1),'_'
                                            ) 
                                        ,'(','_'
                                        )
                                    ,')','_'
                                    )
                                ,'/','_'
                                )
                            ,'&','_'
                            )
                    as Metal_No_Spaces
         into #mytemp_ReadyForRotate
         from #mytemp mt

    select 'This is the content of "#mytemp_ReadyForRotate"' as mynote, * from #mytemp_ReadyForRotate

-- this is for when you KNOW the content:
-- in this query I am able to put the content that has the punctuation in the cell under the appropriate column header

        Select id, pt.Metal_One, Metal_Two, Metal_Four, Metal_Six, Metal_Seven,Metal_Eight
        FROM 
        (
            select * from #mytemp
        ) As Temp
        PIVOT
        (
            max(Metal_01) 
            FOR Metal_02 In(
                                Metal_One,
                                Metal_Two,
                                Metal_Four,
                                Metal_Six,
                                Metal_Seven,
                                Metal_Eight
        )
        ) As pt


-- this is for when you DON'T KNOW the content:
-- in this query I am UNABLE to put the content that has the punctuation in the cell under the appropriate column header
-- unknown as to why it gives me so much grief - just can't get it to work like the above
-- it WORKS just fine but not with the punctuation field
        drop table ##csr_Metals_Rotated
        go

        DECLARE @cols AS NVARCHAR(MAX),
            @query  AS NVARCHAR(MAX),
            @InsertIntoTempTable as nvarchar(4000)

        select @cols = STUFF((SELECT ',' + QUOTENAME(Metal_No_Spaces) 
                            from #mytemp_ReadyForRotate
                            group by Metal_No_Spaces
                            order by Metal_No_Spaces
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

        set @query = 'SELECT id,' + @cols + ' into ##csr_Metals_Rotated from 
                     (
                        select id as id, Metal_No_Spaces 
                        from #mytemp_ReadyForRotate
                    ) x
                    pivot 
                    (
                        max(Metal_No_Spaces)
                        for Metal_No_Spaces in (' + @cols + ')
                    ) p '
        execute(@query);

        select * from ##csr_Metals_Rotated

InformationsquelleAutor user2792497