1


0

TSQLテーブル変換フィールド⇒列

次のテーブルレイアウトがあります。 各行の値は常に一意です。 同じId、Name、Lineのインスタンスが複数存在することはありません。

Id Name Line
1  A    Z
2  B    Y
3  C    X
3  C    W
4  D    W

Lineフィールドが列になるようにデータを照会したいと思います。 値が存在する場合、フィールドデータに1が適用され、そうでない場合は0が適用されます。 e.g.

Id Name Z Y X W
1  A    1 0 0 0
2  B    0 1 0 0
3  C    0 0 1 1
4  D    0 0 0 1

フィールド名W、X、Y、Zはフィールド値の例にすぎないため、たとえば 'X'、 'Y'、または 'Z’などの明示的なチェックに演算子を適用することはできません。 これらはいつでも変更でき、最終的な値のセットに制限されません。 結果セットの列名は、列として一意のフィールド値を反映する必要があります。

どうすればこれを達成できますか?

7 回答


6


これは標準のピボットクエリです。

1がブールインジケータを表す場合-使用:

  SELECT t.id,
         t.name,
         MAX(CASE WHEN t.line = 'Z' THEN 1 ELSE 0 END) AS Z,
         MAX(CASE WHEN t.line = 'Y' THEN 1 ELSE 0 END) AS Y,
         MAX(CASE WHEN t.line = 'X' THEN 1 ELSE 0 END) AS X,
         MAX(CASE WHEN t.line = 'W' THEN 1 ELSE 0 END) AS W
    FROM TABLE t
GROUP BY t.id, t.name

1がグループのその値を持つレコードの数を表す場合、次を使用します。

  SELECT t.id,
         t.name,
         SUM(CASE WHEN t.line = 'Z' THEN 1 ELSE 0 END) AS Z,
         SUM(CASE WHEN t.line = 'Y' THEN 1 ELSE 0 END) AS Y,
         SUM(CASE WHEN t.line = 'X' THEN 1 ELSE 0 END) AS X,
         SUM(CASE WHEN t.line = 'W' THEN 1 ELSE 0 END) AS W
    FROM TABLE t
GROUP BY t.id, t.name


2


問題の更新に続いて*編集*

SQL Serverは動的ピボットをサポートしていません。

これを行うには、動的SQLを使用して、次の行に沿ってクエリを生成できます。

SELECT
       Id ,Name,
       ISNULL(MAX(CASE WHEN Line='Z' THEN 1 END),0) AS Z,
       ISNULL(MAX(CASE WHEN Line='Y' THEN 1 END),0) AS Y,
       ISNULL(MAX(CASE WHEN Line='X' THEN 1 END),0) AS X,
       ISNULL(MAX(CASE WHEN Line='W' THEN 1 END),0) AS W
FROM T
 GROUP BY Id ,Name

または、私が読んだが実際に試したことのない代替方法は、SQL Serverテーブルを指すリンクテーブルでAccessデータベースをセットアップし、SQL ServerからAccessデータベースを照会することにより、Accessの「変換」機能を活用することです!


2


これが動的バージョンです

テストテーブル

create table #test(id int,name char(1),line char(1))

insert #test values(1 , 'A','Z')
insert #test values(2 , 'B','Y')
insert #test values(3 , 'C','X')
insert #test values(4 , 'C','W')
insert #test values(5 , 'D','W')
insert #test values(5 , 'D','W')
insert #test values(5 , 'D','P')

今これを実行する

declare @names nvarchar(4000)

SELECT @names =''
  SELECT  @names    = @names +   line +', '
    FROM (SELECT distinct  line from #test) x

SELECT @names = LEFT(@names,(LEN(@names) -1))

exec('
SELECT *
 FROM(
SELECT DISTINCT Id, Name,Line
FROM #test
    ) AS pivTemp
PIVOT
(   COUNT(Line)
    FOR Line IN (' + @names +' )
) AS pivTable ')

テーブルに1行追加し、上記のクエリを再度実行すると、Bが表示されます

insert #test values(5 , 'D','B')

注意:sp_executeSQLを使用できる場合はもちろん、クエリでそのようなパラメーターが使用されないため、動的SQLのすべての問題が適用されます


1


列挙できるLineの値の数が有限であると仮定します。

declare @MyTable table (
    Id int,
    Name char(1),
    Line char(1)
)

insert into @MyTable
    (Id, Name, Line)
    select 1,'A','Z'
    union all
    select 2,'B','Y'
    union all
    select 3,'C','X'
    union all
    select 3,'C','W'
    union all
    select 4,'D','W'

SELECT Id, Name, Z, Y, X, W
    FROM (SELECT Id, Name, Line
            FROM @MyTable) up
    PIVOT (count(Line) FOR Line IN (Z, Y, X, W)) AS pvt
    ORDER BY Id


0


SQL Serverを使用している場合、この目的のためにhttp://msdn.microsoft.com/en-us/library/ms177410.aspx[PIVOT]演算子を使用することもできます。


0


SQL Server Reporting Services(SSRS)レポートに対してこれを実行している場合、またはレポートを使用するように切り替えることができる場合は、すぐに停止して、Matrixコントロールをレポートにスローします。 ああ! これで終わりです! データがピボットされたハマグリとして幸せ。


0


これはかなりエキゾチックなアプローチです(古いNorthwindデータベースのサンプルデータを使用)。 バージョンhttp://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/cf19f40636353a4c/c107658bfc92ef8a?hl=ja&ie=UTF-8&q=pivot+access+transform+kass#c107658bfc92ef8a [ここ]、DBCC RENAMECOLUMNの廃止とキーワードとしてのPIVOTの追加により機能しなくなりました。

set nocount on
create table Sales (
  AccountCode char(5),
  Category varchar(10),
  Amount decimal(8,2)
)
--Populate table with sample data
insert into Sales
select customerID, 'Emp'+CAST(EmployeeID as char), sum(Freight)
from Northwind.dbo.orders
group by customerID, EmployeeID
create unique clustered index Sales_AC_C
on Sales(AccountCode,Category)
--Create table to hold data column names and positions
select A.Category,
       count(distinct B.Category) AS Position
into #columns
from Sales A join Sales B
on A.Category >= B.Category
group by A.Category
create unique clustered index #columns_P on #columns(Position)
create unique index #columns_C on #columns(Category)
--Generate first column of Pivot table
select distinct AccountCode into Pivoted from Sales
--Find number of data columns to be added to Pivoted table
declare @datacols int
select @datacols = max(Position) from #columns
--Add data columns one by one in the correct order
declare @i int
set @i = 0
while @i < @datacols begin
  set @i = @i + 1
--Add next data column to Pivoted table
  select P.*, isnull((
    select Amount
    from Sales S join #columns C
    on C.Position = @i
    and C.Category = S.Category
    where P.AccountCode = S.AccountCode),0) AS X
  into PivotedAugmented
  from Pivoted P
--Name new data column correctly
  declare @c sysname
  select @c = Category
  from #columns
  where Position = @i
  exec sp_rename '[dbo].[PivotedAugmented].[X]', @c, 'COLUMN'
--Replace Pivoted table with new table
  drop table Pivoted
  select * into Pivoted from PivotedAugmented
  drop table PivotedAugmented
end
select * from Pivoted
go
drop table Pivoted
drop table #columns
drop table Sales