Convertiți tabelul câmpului Bit la tabel

Am un tabel care prezintă locații cu o coloană BIT pentru fiecare instrument utilizat în fiecare locație:

CREATE TABLE dbo.[ToolsSelected] (
    [LocationID] NVARCHAR(40) NOT NULL,
    [Tool1] INTEGER DEFAULT 0 NOT NULL,
    [Tool2] INTEGER DEFAULT 0 NOT NULL,
    [Tool3] INTEGER DEFAULT 0 NOT NULL,
    [Tool4] INTEGER DEFAULT 0 NOT NULL,
    PRIMARY KEY ([LocationID])
);

LocID  Tool1  Tool2  Tool3  Tool4
-----  -----  -----  -----  -----
AZ     0      1      1      0
NY     1      0      1      1

Trebuie să convertesc acest lucru la un tabel cu ajutorul Localizării, indicând ce unelte la care locații:

CREATE TABLE dbo.[ByLocation] (
    [LocationID] NVARCHAR(40) NOT NULL,
    [Tool] NVARCHAR(40) NOT NULL,  -- Column title of ToolsSelected table
    PRIMARY KEY ([LocationID],  [Tool])
);

LocID  Tool
-----  -----
AZ     Tool2
AZ     Tool3
NY     Tool1
NY     Tool3
NY     Tool4

Ideea este că fiecare locație poate selecta instrumentele de care au nevoie, apoi trebuie să interoghez tabelul de instrumente pentru a obține detalii (versiuni etc.) pentru fiecare instrument selectat. Fiecare locație este unică; fiecare instrument este unic. Există o modalitate de a face acest lucru sau o implementare mult mai bună?

0
Acest lucru va fi rulat periodic, deoarece se adaugă locații adiționale care ar putea fi zilnice pe termen scurt. Planul a fost de a muta datele într-o altă tabelă pentru a rula o interogare pentru a obține rezultatele. Am intenționat să ruleze această conversie de fiecare dată când interogarea este rulată pentru a obține cele mai recente date.
adăugat autor Blister, sursa
Ok, cum pot rula o interogare care returnează numele coloanelor și dacă datele sunt adevărate. Dacă ați putea oferi un exemplu, aș aprecia acest lucru.
adăugat autor Blister, sursa
Trebuie să mutați datele o dată, definitiv, la un alt tabel? Sau credeți că trebuie să mutați datele într-un alt tabel pentru a rula o interogare pentru a obține rezultatele pe care le urmăriți? Planificați să executați această conversie de fiecare dată când executați interogarea sau o dată pe zi sau cum intenționați să păstrați cele două tabele în sincronizare? De obicei, nu este înțelept să duplicați informații.
adăugat autor Aaron Bertrand, sursa
Dacă puteți rula o interogare care generează ieșirea dorită la lucruri într-o altă tabelă, de ce nu rulați direct acea interogare direct față de tabela existentă și evitați alta tabelă? Aceasta este concedierea despre care vorbesc - ceea ce crezi că vrei să faci este similar cu a împrumuta 50 de dolari de la John pentru a-i plăti lui John $ 50 pe care îi datorezi. Inventați un om din mijloc fără nici un scop.
adăugat autor Aaron Bertrand, sursa

1 răspunsuri

Iată răspunsul la întrebarea imediată, dat fiind doar 4 coloane de instrumente:

SELECT LocID = LocationID, Tool
FROM
(
    SELECT LocationID, Tool = 'Tool1' FROM dbo.ToolsSelected WHERE Tool1 = 1
    UNION ALL
    SELECT LocationID, Tool = 'Tool2' FROM dbo.ToolsSelected WHERE Tool2 = 1
    UNION ALL
    SELECT LocationID, Tool = 'Tool3' FROM dbo.ToolsSelected WHERE Tool3 = 1
    UNION ALL
    SELECT LocationID, Tool = 'Tool4' FROM dbo.ToolsSelected WHERE Tool4 = 1
) AS x
ORDER BY LocID, Tool;

Cu 40 coloane, ați putea face același lucru, dar împreună cu dorința de a genera acest lucru dinamic:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql += ' 
    UNION ALL 
    SELECT LocationID, Tool = ''' + name + '''
    FROM dbo.ToolsSelected WHERE ' + name + ' = 1'
  FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.ToolsSelected')
  AND name LIKE 'Tool[0-9]%';

SELECT @sql = N'SELECT LocID = LocationID, Tool
    FROM
    (' + STUFF(@sql, 1, 17, '') + '
    ) AS x ORDER BY LocID, Tool;';

PRINT @sql;
-- EXEC sp_executesql @sql;

*BUT*

Depozitarea acestora în coloane separate reprezintă o rețetă pentru dezastru. Deci, când adăugați Tool41, Tool42 etc., trebuie să schimbați schema, apoi să schimbați tot codul care trece numele de coloană și 1/0 prin intermediul parametrilor etc. De ce nu reprezentați aceste numere ca simple, de ex.

CREATE TABLE dbo.LocationTools
(
  LocID  NVARCHAR(40),
  ToolID INT
);

Deci, în cazul de mai sus, ați stoca:

LocID  Tool
-----  ----
AZ     2
AZ     3
NY     1
NY     3
NY     4

Acum, când treceți în casetele de selectare pe care le-ați selectat, probabil din partea frontală primiți două valori, cum ar fi:

LocID: "NY"
Tools: "Tool1, Tool5, Tool26"

Dacă este vorba despre asta, atunci puteți popula masa atunci când un utilizator creează sau modifică alegerea lor, folosind mai întâi o funcție de divizare pentru a descompune lista separată prin virgulă dictată de casetele de selectare:

CREATE FUNCTION dbo.SplitTools
(
  @ToolList NVARCHAR(MAX)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT ToolID = y.i.value('(./text())[1]', 'int')
      FROM 
      ( 
        SELECT x = CONVERT(XML, 
          '' + REPLACE(REPLACE(@List, ',', ''), 'Tool', '') 
          + '').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

(Ați uitat să ne spuneți ce versiune de SQL Server folosiți - dacă în 2008 sau mai mult ați putea utiliza un parametru de valoare ca o alternativă la o funcție split).

Apoi, o procedură pentru ao face:

CREATE PROCEDURE dbo.UpdateLocationTools
  @LocID NVARCHAR(40),
  @Tools NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  -- in case they had previously selected tools
  -- that are no longer selected, clear first:

  DELETE dbo.LocationTools WHERE LocID = @LocID;

  INSERT dbo.LocationTools(LocID, ToolID)
    SELECT @LocID, ToolID
    FROM dbo.SplitTools(@Tools);
END
GO

Acum puteți adăuga un nou instrument #s fără a schimba schema sau codul, deoarece lista dvs. de casete de control ar putea fi, de asemenea, generate din datele dvs. - presupunând că aveți un tabel dbo.Tools sau doriți să adăugați una. Această tabelă ar putea fi utilizată și în scopul integrității datelor (ați putea pune o cheie străină pe dbo.LocationTools.ToolID ).

Și puteți genera interogarea dorită foarte simplu:

SELECT LocID, Tool = 'Tool' + CONVERT(VARCHAR(12), ToolID)
  FROM dbo.LocationTools
  ORDER BY LocID, ToolID;

Nu există date redundante, nu există tabele largi cu coloane nepotrivite și un index adecvat vă pot ajuta chiar să căutați, de exemplu, toate locațiile folosind Tool3 eficient ...

0
adăugat
Mulțumesc, dar sunt de fapt 40 de coloane (instrumente) implicate. Căutam un mijloc dinamic de denaturare a numelor coloanelor.
adăugat autor Blister, sursa
Sunt deschis la orice sugestie de o mai bună implementare. Intenția era să aveți o casetă de selectare simplă pentru ca utilizatorul să selecteze apoi să tragă detaliile pentru utilizarea backend-ului.
adăugat autor Blister, sursa
Mulțumesc. Voi pune în aplicare și vă spun.
adăugat autor Blister, sursa
BTW SQL Server 2005
adăugat autor Blister, sursa
Poate ar trebui să luați în considerare o reproiectare în primul rând. De ce stocați aceste date în 40 de coloane? Mai ales dacă nu doriți să utilizați datele?
adăugat autor Aaron Bertrand, sursa
@Blister vedeți actualizarea mea pentru cum aș proiecta.
adăugat autor Aaron Bertrand, sursa