You must Sign In to post a response.
  • Category: SQL Server

    Write a Query for this suitation

    I have a table like this

    CREATE TABLE #Test(CalDate Datetime, [Time] int, Media VARCHAR(30),[Status] Varchar(30),Duration Int)

    INSERT INTO #Test VALUES('7/1/2014', 32400,'Voice','Login',0)
    INSERT INTO #Test VALUES('7/1/2014', 32400,'Chat','Login',0)
    INSERT INTO #Test VALUES('7/1/2014', 32450,'Voice','Ready',10)
    INSERT INTO #Test VALUES('7/1/2014', 32460,'Chat','Ready',20)
    INSERT INTO #Test VALUES('7/1/2014', 32500,'Voice','Work',30)
    INSERT INTO #Test VALUES('7/1/2014', 32500,'Chat','Work',40)

    And i want output like this:
    CalDate Time, VoiceStatus, VoiceDuration, ChatStatus, Chatduration
    7/1/2014 32400 Login 0 Login 0
    7/1/2014 32450 Ready 10
    7/1/2014 32460 Ready 20
    7/1/2014 32500 Work 30 Work 40
  • #747224
    Hi,

    Use the below SQL script

    CREATE TABLE #test
    (
    caldate DATETIME,
    [time] INT,
    media VARCHAR(30),
    [status] VARCHAR(30),
    duration INT
    )

    INSERT INTO #Test VALUES('7/1/2014', 32400,'Voice','Login',0)
    INSERT INTO #Test VALUES('7/1/2014', 32400,'Chat','Login',0)
    INSERT INTO #Test VALUES('7/1/2014', 32450,'Voice','Ready',10)
    INSERT INTO #Test VALUES('7/1/2014', 32460,'Chat','Ready',20)
    INSERT INTO #Test VALUES('7/1/2014', 32500,'Voice','Work',30)
    INSERT INTO #Test VALUES('7/1/2014', 32500,'Chat','Work',40)


    SELECT DISTINCT caldate,
    time,
    Voice.[status] AS VoiceStatus,
    Voice.duration AS VoiceDuration,
    Chat.[status] AS ChatStatus,
    Chat.duration AS Chatduration
    FROM #test t
    OUTER apply (SELECT status,
    duration
    FROM #test
    WHERE media = 'Voice'
    AND caldate = t.caldate
    AND time = t.time)Voice
    OUTER apply (SELECT status,
    duration
    FROM #test
    WHERE media = 'Chat'
    AND caldate = t.caldate
    AND time = t.time)Chat


    SELECT DISTINCT t.caldate,
    t.time,
    Voice.[status] AS VoiceStatus,
    Voice.duration AS VoiceDuration,
    Chat.[status] AS ChatStatus,
    Chat.duration AS Chatduration
    FROM #test t
    LEFT OUTER JOIN #test Voice
    ON Voice.media = 'Voice'
    AND Voice.caldate = t.caldate
    AND Voice.time = t.time
    LEFT OUTER JOIN #test Chat
    ON Chat.media = 'Chat'
    AND Chat.caldate = t.caldate
    AND Chat.time = t.time

    Let me if you have issues
    Please mark this as Answer, if this helps

  • #747225
    Sandip,

    You are very good in telling the exact requirement. It is very easy for the developers to write a query with the sample data you have provided.

    Please execute the below query;


    SELECT CalDate, [Time]
    , MAX(VoiceStatus) AS VoiceStatus
    , MAX(VoiceDuration) AS VoiceDuration
    , MAX(ChatStatus) AS ChatStatus
    , MAX(Chatduration) AS Chatduration
    FROM
    (
    SELECT CalDate, [Time]
    , CASE WHEN Media = 'voice' THEN [Status] ELSE '' END AS VoiceStatus
    , CASE WHEN Media = 'voice' THEN Duration ELSE '' END AS VoiceDuration
    , CASE WHEN Media = 'chat' THEN [Status] ELSE '' END AS ChatStatus
    , CASE WHEN Media = 'chat' THEN Duration ELSE '' END AS Chatduration
    FROM #test
    ) dt
    GROUP BY CalDate, [Time]

    Please mark this as Answer, if this helps

    Regards,
    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...


  • Sign In to post your comments