Click here to Skip to main content
15,884,177 members
Home / Discussions / Database
   

Database

 
GeneralRe: Handle to chat room with mysql database Pin
data modeling guy16-Jul-14 23:52
data modeling guy16-Jul-14 23:52 
GeneralRe: Handle to chat room with mysql database Pin
Member 1094998617-Jul-14 1:45
Member 1094998617-Jul-14 1:45 
QuestionSSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Pin
Mangesh678616-Jul-14 2:04
Mangesh678616-Jul-14 2:04 
AnswerRe: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Pin
Eddy Vluggen16-Jul-14 5:03
professionalEddy Vluggen16-Jul-14 5:03 
AnswerRe: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Pin
PIEBALDconsult17-Jul-14 13:39
mvePIEBALDconsult17-Jul-14 13:39 
QuestionExtract Data in Single Record Pin
mrfalk15-Jul-14 13:05
mrfalk15-Jul-14 13:05 
AnswerRe: Extract Data in Single Record Pin
data modeling guy15-Jul-14 19:32
data modeling guy15-Jul-14 19:32 
AnswerRe: Extract Data in Single Record Pin
Richard Deeming16-Jul-14 2:42
mveRichard Deeming16-Jul-14 2:42 
Something like this should work:
SQL
WITH cteOrderedContacts As
(
    SELECT
        EmpNum,
        EmpEmergencyContact,
        Relation,
        PhType,
        Phone,
        
        -- Generate a row number for each employee, ordered by contact name:
        ROW_NUMBER() OVER (PARTITION BY EmpNum ORDER BY EmpEmergencyContact) As RN
    FROM
        SourceData
)
SELECT
    C1.EmpNum,
    C1.EmpEmergencyContact As EmergencyContact1,
    C1.Relation As Relation1,
    C1.PhType As PhType1,
    C1.Phone As Phone1,
    C2.EmpEmergencyContact As EmergencyContact2,
    C2.Relation As Relation2,
    C2.PhType As PhType2,
    C2.Phone As Phone2 
FROM
    cteOrderedContacts As C1
    LEFT JOIN cteOrderedContacts As C2
    ON C2.EmpNum = C1.EmpNum  -- Record must be for the same employee;
    And C2.RN = C1.RN + 1     -- Get the next record in the sequence;
WHERE
    -- Only return odd-numbered rows, as the even-numbered rows
    -- will be included in the previous row:
    (C1.RN & 1) = 1
ORDER BY
    C1.EmpNum,
    C1.RN
;


http://www.sqlfiddle.com/#!3/21e9e/4/0[^]



"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


AnswerRe: Extract Data in Single Record Pin
PIEBALDconsult17-Jul-14 13:54
mvePIEBALDconsult17-Jul-14 13:54 
Questionlinked list using CTE Pin
Ali Al Omairi(Abu AlHassan)14-Jul-14 22:29
professionalAli Al Omairi(Abu AlHassan)14-Jul-14 22:29 
AnswerRe: linked list using CTE Pin
Ali Al Omairi(Abu AlHassan)14-Jul-14 23:14
professionalAli Al Omairi(Abu AlHassan)14-Jul-14 23:14 
QuestionOVER (PARTITION BY ORDER BY ) Pin
Ambertje14-Jul-14 4:44
Ambertje14-Jul-14 4:44 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
PIEBALDconsult14-Jul-14 5:13
mvePIEBALDconsult14-Jul-14 5:13 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Ambertje14-Jul-14 5:15
Ambertje14-Jul-14 5:15 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
Eddy Vluggen14-Jul-14 5:27
professionalEddy Vluggen14-Jul-14 5:27 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
PIEBALDconsult14-Jul-14 5:29
mvePIEBALDconsult14-Jul-14 5:29 
QuestionRe: OVER (PARTITION BY ORDER BY ) Pin
Eddy Vluggen14-Jul-14 7:47
professionalEddy Vluggen14-Jul-14 7:47 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
Jörgen Andersson14-Jul-14 8:33
professionalJörgen Andersson14-Jul-14 8:33 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Mycroft Holmes14-Jul-14 12:54
professionalMycroft Holmes14-Jul-14 12:54 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Jörgen Andersson14-Jul-14 22:20
professionalJörgen Andersson14-Jul-14 22:20 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Ambertje14-Jul-14 22:12
Ambertje14-Jul-14 22:12 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
jschell14-Jul-14 10:07
jschell14-Jul-14 10:07 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
GuyThiebaut14-Jul-14 21:33
professionalGuyThiebaut14-Jul-14 21:33 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Ambertje14-Jul-14 22:13
Ambertje14-Jul-14 22:13 
QuestionError: Can't delete row or update row in SQL Server ? Pin
taibc11-Jul-14 18:29
taibc11-Jul-14 18:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.