Sql Server Convert all the Rows into Columns and All the Columns into Rows

Question

I am doing a matching between two types of items say X and Y. And at some point the user would like to change the view, Then I have to make Rows as Cols and Cols as Rows.

Is it possible I can change this (example) table, Here 1 shows a matching exist between X and Y.

Matching |   X1   |   X2  |   X3 
-------    ------   -----   ------
Y1       |   1    |  0    |    0  
------     ------   ------   ----- 
Y2       |   0    |   0   |    1
------     -----   ------   ------
Y3       |   1    |   1   |    1

To This:

Matching |   Y1   |   Y2  |   Y3    
-------    ------   -----   ------
 X1      |   1    |   0   |    1  
------     ------   ------   ----- 
 X2      |   0    |   0   |    1
------     -----   ------   ------
 X3      |   0    |   1   |    1

I have tried with Pivot but every thing is messed up :( . I don't know what to put in Max().

Here is what I tried so far:

SELECT [Matching]
      ,[X1]
      ,[X2]
      ,[X3]

  FROM [Test].[dbo].[Matching_Full]




  Select [Y1] as Y1,
  [Y2] as Y2,
  [Y3] as Y3


  FROM 

  (  Select [Matching]
      ,[X1]
      ,[X2]
      ,[X3]

  FROM [Test].[dbo].[Matching_Full] ) PivotData

  PIVOT

  (
  MAx (Matching)
 FOR Matching IN 

  ([Y1],[Y2],[Y3])

  ) AS Pivoting

And PIVOT is easily search-able on Bing, Google, or whatever you prefer, now that you know what term to search for. ;-) Knowing the term to search is usually the hardest thing to figure out, so Hogan gave you the most valuable inf possible. Too bad he can't get points for up-voted comments. — David, May 02 '14 at 15:04
@DavidStratton - I guess. I'm ok with the rep I have (only 1101 till 30k!) — Hogan, May 02 '14 at 15:07
@Hogan I have tried with Pivot. But I think with Pivot I need to give some column name in Max(). May be I am wrong but if you know anything please correct me. I also have edit the question — Kamran, May 02 '14 at 15:41
@Hogan Yes I think so. If you look at the example table. You can get an idea what I am looking for. And I think that Pivot is not a right solution for this. I could be wrong may be you can give me better suggestion. Thanks! — Kamran, May 02 '14 at 16:00
You need to unpivot and then pivot see this question --- http://stackoverflow.com/questions/15297809/sql-transpose-full-table that does a full transpose of data — Taryn, May 02 '14 at 16:17

score 0 · Answer 1 · answered May 02 '14 at 16:14

I don't know if this might answer your question?

The only problem I can see for this query is that it requires to be updated whenever a new line, let's say Y4, for instance, is added.

But, if your table is to have only three lines, this might help, or put you on the track.

select Matching, sum(Y1) as Y1, sum(Y2) as Y2, sum(Y3) as Y3
       from (
           select 'X1' as Matching
                  , X1 as Y1
                  , null as Y2
                  , null as Y3
              from Matching
              where Matching like 'Y1'
           union
               select 'X2' as Matching
                     , X2
                     , null
                     , null
                  from Matching
                  where Matching like 'Y1'
           union
               select 'X3' as Matching
                       , X3
                       , null
                       , null
                   from Matching
                   where Matching like 'Y1'
           union
               select 'X1' as Matching
                       , null
                       , X1
                       , null
                   from Matching
                  where Matching like 'Y2'
           union
               select 'X2' as Matching
                       , null
                       , X2
                       , null
                   from Matching
                   where Matching like 'Y2'
           union
               select 'X3' as Matching
                       , null
                       , X3
                       , null
                  from Matching
                  where Matching like 'Y2'         
           union
               select 'X1' as Matching
                       , null
                       , null
                       , X1
                   from Matching
                   where Matching like 'Y3'
           union
               select 'X2' as Matching
                       , null
                       , null
                       , X2
                   from Matching
                   where Matching like 'Y3'
           union
               select 'X3' as Matching
                       , null
                       , null
                       , X3
                   from Matching
                   where Matching like 'Y3'         
        ) x
    group by Matching

I remember having such things in the past, about five or six years ago for tables that could have variable number of lines, and I can't seem to remember correctly. One thing is for sure though, I was doing something alike.

The result will be as stated in your question.

Sql Server Convert all the Rows into Columns and All the Columns into Rows

1 Answers1