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.
Updated 30-Apr-19 3:09am

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.

You can achieve the same without using PIVOT statement:
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[^]:
SELECT t1.mailnumber, t1.InboxMessage, t2.SentMessage
SELECT mailnumber, email As InboxMessage
FROM YourTable
WHERE type = 'inbox'
    SELECT mailnumber, email As SentMessage
    FROM YourTable
    WHERE type = 'sent'
) AS t2 ON t1.mailnumber = t2.mailnumber
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
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