Search code examples
sqlt-sqlssms-16

Data Pivoting in SQL using columns of SQL

Following is the Table Structure and below to that is desired output.

Please press Run Code Snippet to see the table structure.

    <html>
    <body>
    <strong>Existing Structure</strong>
    <br>
    <br>
    <table style="width:100%">
      <tr>
        <th>VoucherID</th>
            <th>ColumnName</th>
            <th>ColumnValue</th>

      </tr>
      <tr>
        <td>10</td>
        <td>Buyer Name</td>
        <td>Mr. ABC</td>
      </tr>
      <tr>
        <td>10</td>
        <td>Buyer Address</td>
        <td>Vasant Vihar</td>
      </tr>
      <tr>
        <td>10</td>
        <td>Buyer City</td>
        <td>New Delhi</td>
      </tr>
        <tr>
        <td>10</td>
        <td>Buyer Email</td>
        <td>email@gmail.com</td>
      </tr>
    </table>
<br>
<strong>I want output as follow:-</strong>
<br>
<br>
        <table style="width:100%">
          <tr>
            <th>VoucherID</th>
                <th>Buyer Name</th>
                <th>BuyerAddress</th>
                <th>BuyerCity</th>
                <th>BuyerEmail</th>

          </tr>
          <tr>
            <td>10</td>
            <td>Mr. ABC</td>
            <td>Vasant Vihar</td>
            <td>New Delhi</td>
            <td>email@gmail.com</td>
          </tr>
        </table>
        </body>
        </html>

Hi, I am using SQL Server 2016. Creating dynamic columns from "dbo.TransactionDetails.ColumnName" and taking values from "dbo.TransactionDetails.ColumnValue" where TransactionDetail is Table name and "ColumnName" column contains the name of columns which are to be created dynamically and values in that columns will be taken from "ColumnValue". Please help to get the desired output. if it is possible thorough pivoting or any other way.Guide me through.

What i do is adding multiple Transaction Detail table and get desired output by using "where" which is termed as HARDCODING. but need to do it by pivoting or any other way

Solution

  • Try this one

    create table #Table1
(VoucherID int, ColumnName varchar(100), ColumnValue varchar(255))

insert into #Table1
values (10,'Buyer Name','Mr. ABC')
, (10,'Buyer Address','Vasant Vihar')
, (10,'Buyer City','New Delhi')
, (10,'Buyer Email','email@gmail.com')



select * from #Table1

Select * from 
(
    Select VoucherID,ColumnName,ColumnValue from #Table1
) as src
pivot
(
    MAX(ColumnValue)
    FOR ColumnName IN([Buyer Name],[Buyer Address],[Buyer City],[Buyer Email])
)as pvt

--Drop table #Table1

    enter image description here