Microsoft SQL Server
PIVOT / UNPIVOT

वाक्य - विन्यास

• चुनें <non-pivoted column> ,
  [पहला स्तंभित कॉलम] AS <column name> ,
  [दूसरा स्तंभित कॉलम] AS <column name> ,
  ...
  [अंतिम स्तंभित कॉलम] AS <column name>
  से
  ( <SELECT query that produces the data> )
  <alias for the source query>
  धुरी
  (
  <aggregation function> ( <column being aggregated> )
  के लिये
  [ <column that contains the values that will become column headers> ]
  ([पहला पिवोटेड कॉलम], [दूसरा पिवेटेड कॉलम],
  ... [अंतिम पृष्ठांकित कॉलम])
  ) के रूप में <alias for the pivot table> <optional ORDER BY clause> ;

टिप्पणियों

सरल धुरी - स्टेटिक कॉलम

उदाहरण डेटाबेस से आइटम की बिक्री तालिका का उपयोग करते हुए, हम गणना करते हैं और प्रत्येक उत्पाद की कुल मात्रा दिखाते हैं।

यह आसानी से एक समूह के साथ किया जा सकता है, लेकिन हमें मान देता है कि हम अपने परिणाम तालिका को इस तरह से 'घुमाएं' करें कि प्रत्येक उत्पाद आईडी के लिए हमारे पास एक कॉलम हो।

SELECT [100], [145]
  FROM (SELECT ItemId , Quantity
          FROM #ItemSalesTable
       ) AS pivotIntermediate
 PIVOT (   SUM(Quantity)
           FOR ItemId IN ([100], [145])
       ) AS pivotTable

चूंकि हमारे 'नए' कॉलम नंबर हैं (स्रोत तालिका में), हमें वर्ग कोष्ठक की आवश्यकता है []

यह हमें जैसे आउटपुट देगा

सिंपल PIVOT & UNPIVOT (T-SQL)

नीचे एक सरल उदाहरण दिया गया है जो प्रति सप्ताह प्रत्येक आइटम की औसत कीमत दर्शाता है।

पहला, मान लीजिए कि हमारे पास एक तालिका है जो सभी वस्तुओं की कीमतों का दैनिक रिकॉर्ड रखती है।

CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT);

INSERT INTO tbl_stock VALUES 
('Item1', 'Mon', 110), ('Item2', 'Mon', 230), ('Item3', 'Mon', 150), 
('Item1', 'Tue', 115), ('Item2', 'Tue', 231), ('Item3', 'Tue', 162), 
('Item1', 'Wed', 110), ('Item2', 'Wed', 240), ('Item3', 'Wed', 162), 
('Item1', 'Thu', 109), ('Item2', 'Thu', 228), ('Item3', 'Thu', 145), 
('Item1', 'Fri', 120), ('Item2', 'Fri', 210), ('Item3', 'Fri', 125),
('Item1', 'Mon', 122), ('Item2', 'Mon', 225), ('Item3', 'Mon', 140),
('Item1', 'Tue', 110), ('Item2', 'Tue', 235), ('Item3', 'Tue', 154),
('Item1', 'Wed', 125), ('Item2', 'Wed', 220), ('Item3', 'Wed', 142);

तालिका नीचे की तरह दिखनी चाहिए:

+========+=========+=======+
|   item | weekday | price |
+========+=========+=======+
|  Item1 |    Mon  |   110 |
+--------+---------+-------+
|  Item2 |    Mon  |   230 |
+--------+---------+-------+
|  Item3 |    Mon  |   150 |
+--------+---------+-------+
|  Item1 |    Tue  |   115 |
+--------+---------+-------+
|  Item2 |    Tue  |   231 |
+--------+---------+-------+
|  Item3 |    Tue  |   162 |
+--------+---------+-------+
|          . . .           |
+--------+---------+-------+
|  Item2 |    Wed  |   220 |
+--------+---------+-------+
|  Item3 |    Wed  |   142 |
+--------+---------+-------+

एकत्रीकरण करने के लिए, जो प्रत्येक सप्ताह के लिए प्रति आइटम औसत मूल्य का पता लगाना है, हम संबंधपरक ऑपरेटर PIVOT का उपयोग तालिका-मानित अभिव्यक्ति के स्तंभ weekday को कुल पंक्ति मानों में बदलने के लिए करने जा रहे हैं:

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt;

परिणाम:

+--------+------+------+------+------+------+
|  item  |  Mon |  Tue |  Wed |  Thu |  Fri |
+--------+------+------+------+------+------+
|  Item1 |  116 |  112 |  117 |  109 |  120 |
|  Item2 |  227 |  233 |  230 |  228 |  210 |
|  Item3 |  145 |  158 |  152 |  145 |  125 |
+--------+------+------+------+------+------+

अंत में, PIVOT के रिवर्स ऑपरेशन को करने के लिए, हम संबंधित ऑपरेटर UNPIVOT का उपयोग कॉलम को नीचे की पंक्तियों में घुमाने के लिए कर सकते हैं:

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt
UNPIVOT ( 
    price FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) unpvt;

परिणाम:

+=======+========+=========+
|  item |  price | weekday |
+=======+========+=========+
| Item1 |    116 |     Mon |
+-------+--------+---------+
| Item1 |    112 |     Tue |
+-------+--------+---------+
| Item1 |    117 |     Wed |
+-------+--------+---------+
| Item1 |    109 |     Thu |
+-------+--------+---------+
| Item1 |    120 |     Fri |
+-------+--------+---------+
| Item2 |    227 |     Mon |
+-------+--------+---------+
| Item2 |    233 |     Tue |
+-------+--------+---------+
| Item2 |    230 |     Wed |
+-------+--------+---------+
| Item2 |    228 |     Thu |
+-------+--------+---------+
| Item2 |    210 |     Fri |
+-------+--------+---------+
| Item3 |    145 |     Mon |
+-------+--------+---------+
| Item3 |    158 |     Tue |
+-------+--------+---------+
| Item3 |    152 |     Wed |
+-------+--------+---------+
| Item3 |    145 |     Thu |
+-------+--------+---------+
| Item3 |    125 |     Fri |
+-------+--------+---------+

गतिशील PIVOT

PIVOT क्वेरी के साथ एक समस्या यह है कि आपको IN चयन के अंदर सभी मान निर्दिष्ट करने होंगे यदि आप उन्हें कॉलम के रूप में देखना चाहते हैं। इस समस्या को दरकिनार करने का एक त्वरित तरीका यह है कि आप अपने PIVOT गतिशील बनाते हुए एक गतिशील IN चयन करें।

प्रदर्शन के लिए हम Bookstore के डेटाबेस में एक टेबल Books उपयोग करेंगे। हम मानते हैं कि तालिका काफी सामान्य है और निम्नलिखित कॉलम हैं

Table: Books
-----------------------------
BookId (Primary Key Column)
Name
Language
NumberOfPages
EditionNumber
YearOfPrint
YearBoughtIntoStore
ISBN
AuthorName
Price
NumberOfUnitsSold

तालिका के लिए निर्माण स्क्रिप्ट निम्नानुसार होगी:

CREATE TABLE [dbo].[BookList](
      [BookId] [int] NOT NULL,
      [Name] [nvarchar](100) NULL,
      [Language] [nvarchar](100) NULL,
      [NumberOfPages] [int] NULL,
      [EditionNumber] [nvarchar](10) NULL,
      [YearOfPrint] [int] NULL,
      [YearBoughtIntoStore] [int] NULL,
[NumberOfBooks] [int] NULL,
[ISBN] [nvarchar](30) NULL,
      [AuthorName] [nvarchar](200) NULL,
      [Price] [money] NULL,
      [NumberOfUnitsSold] [int] NULL,
 CONSTRAINT [PK_BookList] PRIMARY KEY CLUSTERED
(
      [BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

अब अगर हमें डेटाबेस पर क्वेरी करनी है और हर साल अंग्रेजी, रूसी, जर्मन, हिंदी, लैटिन भाषाओं की किताबों की संख्या बुकस्टोर में खरीदनी है और अपने आउटपुट को छोटे रिपोर्ट प्रारूप में प्रस्तुत करना है, तो हम इस तरह PIVOT क्वेरी का उपयोग कर सकते हैं।

SELECT * FROM
  (
   SELECT YearBoughtIntoStore AS [Year Bought],[Language], NumberOfBooks
   FROM BookList
  ) sourceData 
 PIVOT
  (
  SUM(NumberOfBooks)
  FOR [Language] IN (English, Russian, German, Hindi, Latin)
  ) pivotrReport

विशेष मामला तब होता है जब हमारे पास भाषाओं की पूरी सूची नहीं होती है, इसलिए हम नीचे की तरह गतिशील SQL का उपयोग करेंगे

DECLARE @query VARCHAR(4000)
DECLARE @languages VARCHAR(2000)
SELECT @languages =
        STUFF((SELECT DISTINCT '],['+LTRIM([Language])FROM [dbo].[BookList]
        ORDER BY '],['+LTRIM([Language]) FOR XML PATH('') ),1,2,'') + ']'
SET @query=
'SELECT * FROM
  (SELECT YearBoughtIntoStore AS [Year Bought],[Language],NumberOfBooks
   FROM BookList) sourceData
PIVOT(SUM(NumberOfBooks)FOR [Language] IN ('+ @languages +')) pivotrReport' EXECUTE(@query)