Konvertieren von Zeilen in Spalten in einer Sql-Abfrage

Ich habe harte Zeit, herauszufinden, diese bitte mir helfen
Ich habe mal in/aus sql-Abfrage
Ich habe eine Tabelle sieht wie folgt aus. es gibt vier Spalten zeigt die Zeit in/out info wie...

Date Day    Day  TimeStamp CheckType   
10/11/2014  Sat  8:30 am    in
10/11/2014  Sat  11:30am    out
10/11/2014  Sat  1:30pm     in
10/11/2014  Sat             out
10/12/2014  Sun  9:00am     in
10/12/2014  Sun  11:20pm    out
10/12/2014  Sun  5:20pm     out
10/13/2014  Mon  8:00am     in
10/13/2014  Mon  6:10pm     in

also wer checkin oder checkout, dann wird der Datensatz-Anzeige das Ergebnis in Ordnung und wenn jemand soll, heraus zu überprüfen, aber versehentlich gedrückt Schaltfläche, dann wird das display wie es ist (in), oder wenn jemand vergessen, dass Sie dann, dass der Raum zeigt leere

Ich versuche konvertieren von Zeilen in Spalten-und Darstellung dieser Informationen in unten

Date       Day   Time  Type  Time   Type Time  Type  Time  Type etc-----
10/11/2014 Sat  8:30am in  11:30am  out 1:30pm  in     
10/12/2014 Sun  9:00am in  11:20am  out 1:20pm  in  6:20pm in
10/13/2014 Mon  8:00am in  6:10pm   out
10/14/2014 Tus  8:20am in
etc

Habe ich versucht, pivot - 

select Date, Day, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] etc---
from
(
select Date, Day, Stamptime, CheckTime, userID
from a table
)
pivot
(
    max(StampTime)
    for stamptime in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10] etc---)
) as PivotTable

kann mir jemand erklären, wie konvertieren von Zeilen in Spalten ich habe verbrachte viele Tage schon.

  • SQL Fiddle: sqlfiddle.com/#!6/0174d (mit ein paar Optimierungen, die auf der original-Tabelle)
  • Sie können das nicht (in der Datenbank, in der UI ist OK). Das Problem ist an einem Tag können Sie haben 1 check-in, am nächsten Tag haben Sie möglicherweise 2 check-ins und 1 check-out. Das bedeutet, dass die Anzahl der erforderlichen Spalten für den ersten Tag ist anders, um die erforderliche Anzahl für die zweite. Es bedeutet auch, Sie nicht wissen, die maximale Anzahl der Spalten, die Sie benötigen. Sie können in der Lage sein zu umgehen, indem man die Ausgabe in XML anstelle von Daten der Tabelle, aber wirklich, ich glaube, du bist die Schaffung Schmerz für dich / sollten überdenken Ihre Herangehensweise an das problem.
InformationsquelleAutor Steve | 2014-10-12
  1. 2

    Hier ist etwas in der Nähe, was Sie nach, machen Verwendung von XML, um die variable Anzahl der Spalten. Wie bereits in meinem Kommentar oben, aber ich weiß nicht empfehlen diese Vorgehensweise.
    SQL Fiddle: http://sqlfiddle.com/#!3/e5b325/2

    select [Date]
, [Day]
, (
    select [TimeStamp] [Time]
    , [CheckType] [Type]
    from aTable b
    where b.[Date] = a.[Date]
    order by [TimeStamp], [CheckType]
    for xml path ('')
) CheckInAndOutInfo
from aTable a
group by [Date], [Day]
order by [Date]

    Ausgabe:

    DATE        DAY CHECKINANDOUTINFO
2014-10-11  Sat <Type>Out</Type><Time>08:30:00</Time><Type>In</Type><Time>11:30:00</Time><Type>Out</Type><Time>13:30:00</Time><Type>In</Type>
2014-10-12  Sun <Time>09:00:00</Time><Type>In</Type><Time>17:20:00</Time><Type>Out</Type><Time>23:20:00</Time><Type>Out</Type>
2014-10-13  Mon <Time>08:00:00</Time><Type>In</Type><Time>18:10:00</Time><Type>In</Type>

    Alternativ, wenn Sie eine Garantie haben Sie nie mehr als eine bestimmte Anzahl von check-ins/- outs pro Tag, könnten Sie Folgendes tun (dies nimmt nicht mehr als 5 pro Tag):
    SQL Fiddle: http://sqlfiddle.com/#!3/e5b325/4

    select *
from
(
    select [Date], [Day]
    , 'T' + CAST(ROW_NUMBER() over (partition by [Date] order by [TimeStamp], [CheckType]) as nvarchar) r
    ,  cast([TimeStamp] as nvarchar) pvtVal
    from aTable
    where [TimeStamp] is not null


    union all

    select [Date], [Day]
    , 'C' + CAST(ROW_NUMBER() over (partition by [Date] order by [TimeStamp], [CheckType]) as nvarchar) r
    ,  cast([CheckType] as nvarchar) pvtVal
    from aTable
    where [TimeStamp] is not null
) x
pivot
(
    min(pvtVal)
    for r in ([T1], [C1], [T2], [C2], [T3], [C3], [T4], [C4], [T5], [C5])
) y
order by [Date]

    Ausgabe:

    DATE        DAY  T1                C1  T2                C2   T3                C3      T4      C4      T5      C5
2014-10-11  Sat  08:30:00.0000000  In  11:30:00.0000000  Out  13:30:00.0000000  In      (null)  (null)  (null)  (null)
2014-10-12  Sun  09:00:00.0000000  In  17:20:00.0000000  Out  23:20:00.0000000  Out     (null)  (null)  (null)  (null)
2014-10-13  Mon  08:00:00.0000000  In  18:10:00.0000000  In   (null)            (null)  (null)  (null)  (null)  (null)

    ...Oder wollten Sie dynamisches SQL verwenden, Sie könnten dies tun:
    SQL Fiddle: http://sqlfiddle.com/#!3/e5b325/6

    declare @sql nvarchar(max)
select @sql = coalesce(@sql+',','') + QUOTENAME('T' + CAST(x as nvarchar)) + ',' + QUOTENAME('C' + CAST(x as nvarchar))
from
(
    select distinct row_number() over (partition by [Date] order by [Date]) x 
    from aTable 
    where [TimeStamp] is not null
) y
order by x

set @sql =
    'select *
    from
    (
        select [Date], [Day]
        , ''T'' + CAST(ROW_NUMBER() over (partition by [Date] order by [TimeStamp], [CheckType]) as nvarchar) r
        ,  cast([TimeStamp] as nvarchar) pvtVal
        from aTable
        where [TimeStamp] is not null

        union all

        select [Date], [Day]
        , ''C'' + CAST(ROW_NUMBER() over (partition by [Date] order by [TimeStamp], [CheckType]) as nvarchar) r
        ,  cast([CheckType] as nvarchar) pvtVal
        from aTable
        where [TimeStamp] is not null
    ) x
    pivot
    (
        min(pvtVal)
        for r in (' + @sql + ')
    ) y
    order by [Date]'

exec (@sql)

    Ausgabe:

    DATE        DAY  T1                C1  T2                C2   T3                C3
2014-10-11  Sat  08:30:00.0000000  In  11:30:00.0000000  Out  13:30:00.0000000  In
2014-10-12  Sun  09:00:00.0000000  In  17:20:00.0000000  Out  23:20:00.0000000  Out
2014-10-13  Mon  08:00:00.0000000  In  18:10:00.0000000  In   (null)            (null)
    • Fantastisch!! Ich danke Ihnen so sehr Ihre option 2 hat Super funktioniert. Sie sind der Mann. Jedoch, ich habe versucht, die Umsetzung der option 3 und ich habe aber schon immer ein Fehler, sagt Msg-137, Ebene 15, Status 2, Zeile 20 Muss erklären, die Skalare variable "@DomainId". Msg 137, Ebene 15, Status 2, Zeile 44 Muss erklären, die Skalare variable "@DomainId".
    • Hey, keine Sorge. @DomainId erwähnt ist nicht in meinem Skript; also ich nehme an, Sie haben geändert, das Skript und setzen den Wert dort irgendwo. Entweder müssen Sie eine declare-Anweisung (so etwas wie declare @DomainId bigint = 123), oder man deklariert eine variable und dann versucht haben, es zu benutzen, die in der dynamischen SQL; in diesem Fall müssen Sie einen Blick in sp_executesql (statt exec) es als parameter übergeben; msdn.microsoft.com/en-gb/library/ms188001.aspx. Suche oder Frage als eine neue Frage auf, SO mit dem code, wenn man auf Fragen gibt.
    InformationsquelleAutor JohnLBevan
Schreibe einen Kommentar