-1

I have tables Employees, Documents, Transactions, from_to table.

Employee can send a document to other employee and send a copy of the document to other employee. 

data will be displayed in transaction table as follow: 

TransId  -   Document Id -   EmployeeId - from_toId

1                1                5           1 (From)
2                1                6           2 (To)
3                1                10          2 (CC)

Now; I want the data above to be displayed as follow: 

DocId         From         To          CC

1             Jo(5)       Fo(6)       Do(10)

I know that we need something to do with "Pivot Table". BUT I DON'T KNOW HOW.

Waiting for your feedback.

daniula
  • 6,898
  • 4
  • 32
  • 49
user3713056
  • 1
  • 1
  • 1

2 Answers2

1

I have a solution with a Cursor which is dynamic:

CREATE TABLE #PRERESULT(
[TransId] int,
DocumentID  int ,
EmployeeId int ,
from_toId [nvarchar](10)
) 

INSERT INTO #PRERESULT ([TransId],DocumentID,EmployeeId,from_toId)
VALUES
(1,1,5,'1 (From)'),
(2,1,6,'2 (To)'),
(3,1,10,'2 (CC)')

CREATE TABLE #RESULT (
DocID int,
[From] nvarchar(15),
[To] nvarchar(15),
CC nvarchar(15))

INSERT INTO #RESULT (DocID)
SELECT DocumentID
FROM #PRERESULT
GROUP BY DocumentID

DECLARE @Documentid int,@Employee int, @Alias nvarchar(10),@SQL nvarchar(250)

DECLARE C_FromTo CURSOR
FOR
    SELECT DocumentID,EmployeeID
    FROM #PRERESULT

OPEN C_FromTo


FETCH NEXT FROM C_FromTo INTO @Documentid, @Employee
While (@@Fetch_status = 0)
BEGIN

SET @Alias = (SELECT SUBSTRING(from_toId,PATINDEX('%(%',from_toId)+1,(LEN(from_toId)-PATINDEX('%(%',from_toId)-1)) FROM #PRERESULT WHERE @Employee = EmployeeId)

SET @SQL = 'UPDATE #RESULT
SET ['+@Alias+'] = '+Convert(nvarchar(50),@Employee)+'
WHERE '+Convert(nvarchar(50),@Documentid)+' = DocID'

EXEC (@SQL)

FETCH NEXT FROM C_FromTo INTO @Documentid, @Employee
END
CLOSE C_FromTo
DEALLOCATE C_FromTO

SELECT * FROM #RESULT


DROP TABLE #PRERESULT
DROP TABLE #RESULT

Gives you this:

DocID |From |To |CC
1         |5       |6   |10

Hope this will help you
Have a nice day & Greets from Switzerland

Etienne

Tienou
  • 700
  • 6
  • 17
0

I think this reflects your table, although I am using names instead of IDs for the last two columns: 

CREATE TABLE [dbo].[Transaction](
            [TransId] [int] NOT NULL,
            [DocId] [int] NOT NULL,
            [EmpId] [nvarchar](10) NOT NULL,
            [FromToId] [nchar](10) NOT NULL
) ON [PRIMARY]

INSERT INTO [Transaction] ([TransId],[DocId],[EmpId],[FromToId])VALUES(1,1,'Jo','From')
INSERT INTO [Transaction] ([TransId],[DocId],[EmpId],[FromToId])VALUES(2,1,'Fo','To')
INSERT INTO [Transaction] ([TransId],[DocId],[EmpId],[FromToId])VALUES(3,1,'Do','CC')
INSERT INTO [Transaction] ([TransId],[DocId],[EmpId],[FromToId])VALUES(4,2,'Jo','From')
INSERT INTO [Transaction] ([TransId],[DocId],[EmpId],[FromToId])VALUES(5,2,'Bo','To')
INSERT INTO [Transaction] ([TransId],[DocId],[EmpId],[FromToId])VALUES(6,2,'Zo','CC')
INSERT INTO [Transaction] ([TransId],[DocId],[EmpId],[FromToId])VALUES(7,3,'Bo','From')
INSERT INTO [Transaction] ([TransId],[DocId],[EmpId],[FromToId])VALUES(8,3,'Go','To')

Then this query will give you the results you requested:

SELECT DISTINCT
       t.DocId
      ,x.[From]
      ,y.[To]
      ,z.Cc
FROM [Transaction] t
LEFT JOIN 
(
    SELECT DocId 
   ,CASE WHEN FromToId = 'From' THEN EmpId END AS [From]
    FROM [Transaction]
    WHERE CASE WHEN FromToId = 'From' THEN EmpId END IS NOT NULL
) x ON t.DocId = x.DocId 
LEFT JOIN 
(
    SELECT DocId 
   ,CASE WHEN FromToId = 'To'   THEN EmpId END AS [To]
    FROM [Transaction]
    WHERE CASE WHEN FromToId = 'To'   THEN EmpId END IS NOT NULL
) y ON t.DocId = y.DocId 
LEFT JOIN 
(
    SELECT DocId 
   ,CASE WHEN FromToId = 'CC'   THEN EmpId END AS [Cc]
    FROM [Transaction]
    WHERE CASE WHEN FromToId = 'Cc'   THEN EmpId END IS NOT NULL
) z ON t.DocId = z.DocId 

  DocId   From  To  Cc
  1       Jo    Fo  Do
  2       Jo    Bo  Zo
  3       Bo    Go  NULL
wizardofgaz
  • 11
  • 2