SQLTeam.com | Weblogs | Forums

From column to row


#1

I have data like this

tbDocument
No  Document
1    AAA

tbApproval
No  Ref_No  Document  Sign_Path
1     1        AAA    D:\Sign\A.jpg
2     1        AAA    D:\Sign\B.jpg
3     1        AAA    D:\Sign\C.jpg

My queries:

SELECT * FROM tbDocument A
INNER JOIN tbApproval B ON A.No = B.Ref_No
WHERE A.No = 1

How I can get the result like this:

No Document      Sign1         Sign2         Sign3
1   AAA      D:\Sign\A.jpg  D:\Sign\B.jpg D:\Sign\C.jpg

Thank you for your help.


#2

See JamesK's answer in this thread: Dynamic Pivots


#3

hello , i try to use pivot and follow JamesK Answer,
here is my query :

;WITH CTE AS 
(
      SELECT ,NAME,JOB_LEVEL_POSITION,DIGITAL_SIGN
            DENSE_RANK() OVER (ORDER BY SEQUENCE DESC) AS RN
      FROM data_employee
)
SELECT * FROM CTE
PIVOT (MAX(DIGITAL_SIGN) FOR RN IN ([1],[2],[3],[4],[5],[6],[7],[8]))P

and the result :

NAME JOB_POSITION 1   2   3   4
BRO    DEV	XXXX NULL NULL NULL
Tony   QA	 NULL XXXD NULL NULL
Momo   MGR	 NULL NULL YYYY NUL

L

the problem is,
i need to select column like this :

my data : 
Data Employee
    Name         Position        Sign    Sequence
    Bro               DEV         XXXX        1
    Tony              QA          XXXD        2
    Momo              MGR        YYYY         3

and i want the result like this:

 Name1 Name2 Name3  Position1 Position2 Position3   Sign1  Sign Sign3
 Bro   Tony   Momo      DEV      QA         MGR      XXXX  XXXD  YYYY 

thank you for your help