Click here to Skip to main content
15,066,651 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I would appreciate if someone could guide me with query I need to perform to create the table.
This is my situation,

my data in the table is like this

mailnumber, type,email
 1,        inbox, some inbox message..
 1,        sent, some sent message..
 2,        inbox,inbox message
 2,        sent, sent message



What do i want to achieve is that I want to pivot this data into

mailnumber, inboxMessage,          SentMessage
 1,          some inbox message..,  some sent message..
 2,          inbox message..,        sent message...



and insert the data into new table

What I have tried:

I tried to do with SSIS eventhough I am very new to SSIS but due to data length It did not let me do it.
Posted
Updated 30-Apr-19 3:09am
v2

1 solution

Start with reading documentation:
Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]
INSERT (Transact-SQL) - SQL Server | Microsoft Docs[^]
When get stuck, come back here and ask detailed question.

[EDIT]
You can achieve the same without using PIVOT statement:
SQL
SELECT mailnumber, MAX(CASE WHEN [type] = 'inbox' THEN email ELSE NULL END) As InboxMessage, MAX(CASE WHEN [type] = 'sent' THEN email ELSE NULL END) AS SentMessage
FROM YourTable
GROUP BY mailnumber;


[EDIT #2]
Another way - using Joins[^]:
SQL
SELECT t1.mailnumber, t1.InboxMessage, t2.SentMessage
FROM (
SELECT mailnumber, email As InboxMessage
FROM YourTable
WHERE type = 'inbox'
) AS t1 INNER JOIN (
    SELECT mailnumber, email As SentMessage
    FROM YourTable
    WHERE type = 'sent'
) AS t2 ON t1.mailnumber = t2.mailnumber
   
v4
Comments
istudent 30-Apr-19 9:12am
   
These example use aggregate functions. I checked this and read other blogs as well before I had posted the my problem.
Maciej Los 30-Apr-19 9:19am
   
And...
istudent 30-Apr-19 9:21am
   
In above situation I do not need to use any aggregate function. Do I?
Maciej Los 30-Apr-19 9:31am
   
It depends...
The only way to not use aggregation functions is to use joins. Please, check updated answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900