Convert columns to rows based on condition in SQL Server

Question

Below is the table design

CustId   Ver    VersionNo   Version ResNO   Res     Name    Path
---------------------------------------------------------------------------
2        VF         2       V2        1     Low     temp    a/PV_7_temp.pdf
2        VF         2       V2        2     High    temp    a/temp/temp.pdf

If user enters path as 'a/PV_7_temp.pdf' then result should be:

CustId  Path
--------------------------
2        VF                  ---ver
2        V2                  ---version
2        High                ----Res
2        a/PV_7_temp.pdf     -----path

If user enters path as 'a/temp/temp.pdf' then result should be:

CustId  Path
-------------------------
2        VF
2        V2
2        Low
2       a/temp/temp.pdf

Please help me get these desired results in SQL Server 2014

Answer 1

using cross apply(values ...) to unpivot your data:

declare @Path varchar(128) = 'a/temp/temp.pdf';

select t.CustId, u.Path
from t
  cross apply (values (1,Ver),(2,Version),(3,Res),(4,Path)) u(Ordinal, Path)
where t.Path = @Path
order by t.CustId, u.Ordinal

You really need something to order by based on your example. Is there a reason you do not want to include a column specifying where each value comes from?

rextester demo: http://rextester.com/JEBMGH56691

returns:

+--------+-----------------+
| CustId |      Path       |
+--------+-----------------+
|      2 | VF              |
|      2 | V2              |
|      2 | High            |
|      2 | a/temp/temp.pdf |
+--------+-----------------+