CODEWITHSUNDEEP
sqlsql-servert-sql
Lynchie
Lynchie

Reputation: 1149

SQL PIVOT - Data Formatting Query

I have some data from a provider where unfortunately they don't have a true relational db. Basically I have a Reference, and 5 columns that are accident dates. Now there can be multiple entries in the table because each 'frame' in the software can only show 5 accident dates.

If they have more than 5 accidents then an additional row is created as that will be another frame.

Below is an example I have put together of a client that has 3 accident frames with 2 x frames with 5 accidents and 1 frame with 1.

B@          PolRef@    Acc_date1               Acc_date2               Acc_date3                  Acc_date4               Acc_date5
----------- ---------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
0           LALX05PC01 2006-07-14 00:00:00.000 NULL                    NULL                    NULL                    NULL
0           LALX05PC01 2005-11-01 00:00:00.000 2007-06-07 00:00:00.000 2007-09-15 00:00:00.000 2007-09-16 00:00:00.000 2007-09-17 00:00:00.000
0           LALX05PC01 2005-11-01 00:00:00.000 2007-06-07 00:00:00.000 2007-06-08 00:00:00.000 2007-06-09 00:00:00.000 2007-06-10 00:00:00.000

I believe it will be a SQL PIVOT but I am still green with SQL and haven't got my head around PIVOT in SQL yet.

I need the data to end up that the Accident Dates are actually rows instead of columns for example

B@          PolRef@    AccidentDate
----------- ---------- -----------------------
0           LALX05PC01 2006-07-14 00:00:00.000
0           LALX05PC01 2005-11-01 00:00:00.000
0           LALX05PC01 2007-06-07 00:00:00.000
0           LALX05PC01 2007-09-15 00:00:00.000

Etc. there are additional columns I could do with inserting onto each line that are in the same fashion as Acc_Date1, Acc_Date2 etc but I feel it fair to ask for assistance and attempt to actually do some work myself :)

If anyone can help me I would greatly appreciate it.

Regards,

James

Upvotes: 0

Views: 204

Answers (1)

Youssef DAOUI
Youssef DAOUI

Reputation: 329

Here's your answer :

    /*Part 1 : Create a table test for exemple to test the Table-valued Function */
IF OBJECT_ID('Temp') IS NOT NULL
    DROP TABLE Temp

SELECT *
INTO Temp
FROM(

SELECT 'James' Name, CONVERT(DATE,'2012-01-01') AS Date_1, ('Desc 1 2012-01-01') AS Desc_1,     CONVERT(DATE,'2012-01-03') AS     Date_2,('Desc 2 2012-01-03') AS Desc_2, CONVERT(DATE,'2012-01-04')     AS Date_3,('Desc 3 2012-01-04') AS Desc_3 UNION ALL

SELECT 'James' Name, CONVERT(DATE,'2012-01-03') AS Date_1, ('Desc 1 2012-01-03') AS Desc_1,NULL     AS     Date_2,NULL AS Desc_2, CONVERT(DATE,'2012-01-02') AS Date_3,('Desc 3 2012-01-    02') AS Desc_3 UNION ALL
SELECT 'James' Name, CONVERT(DATE,'2012-01-07') AS Date_1, ('Desc 1 2012-01-07') AS Desc_1, NULL     AS     Date_2,NULL AS Desc_2, NULL AS Date_3,NULL AS Desc_3 UNION ALL
SELECT 'James' Name, CONVERT(DATE,'2012-02-01') AS Date_1, ('Desc 1 2012-02-01') AS Desc_1, NULL     AS     Date_2,NULL AS Desc_2, NULL AS Date_3,NULL AS Desc_3 UNION ALL

SELECT 'Youssef' Name, CONVERT(DATE,'2012-01-01') AS Date_1, ('Desc 1 2012-01-01') AS Desc_1,     NULL AS     Date_2,NULL AS Desc_2, CONVERT(DATE,'2012-01-04') AS Date_3,('Desc 3 2012-01-    04') AS Desc_3 UNION ALL
SELECT 'James' Name, CONVERT(DATE,'2012-01-12') AS Date_1, ('Desc 1 2012-01-12') AS Desc_1,     CONVERT(DATE,'2012-01-04') AS     Date_2,('Desc 2 2012-01-04') AS Desc_2, CONVERT(DATE,'2012-01-04')     AS Date_3,('Desc 3 2012-01-04') AS Desc_3 UNION ALL
SELECT 'Youssef' Name, CONVERT(DATE,'2012-01-01') AS Date_1, ('Desc 1 2012-01-01') AS Desc_1,     CONVERT(DATE,'2012-01-03') AS     Date_2,('Desc 2 2012-01-03') AS Desc_2, CONVERT    (DATE,'2013-01-01') AS Date_3,('Desc 3 2013-01-01') AS Desc_3
 )AS T

GO




/* Part 2 : Creating  the function that will allow us to create a DataSet of dates of     accidents     related to the Person */
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].    [FN_GetAccidentDates]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FN_GetAccidentDates]
GO

CREATE FUNCTION [dbo].[FN_GetAccidentDates](@p_name NVARCHAR(50))
RETURNS  @table_result TABLE ([Dates] DATETIME,[Desciption] NVARCHAR(255))
AS
BEGIN

    INSERT INTO @table_result([Dates], [Desciption])
    SELECT vDate, vDesciption
    FROM
        (SELECT DISTINCT Date_1 AS vDate, Desc_1 AS vDesciption  FROM Temp WHERE Name = @p_name
            UNION
        SELECT DISTINCT Date_2 AS vDate, Desc_2 AS vDesciption FROM Temp WHERE Name = @p_name
            UNION
        SELECT DISTINCT Date_3 AS vDate, Desc_3 AS vDesciption FROM Temp WHERE Name = @p_name
        ) AS T
    WHERE
         vDate IS NOT NULL

    RETURN
END
GO


/*Part 3 Test the function and get the List Of dates */
SELECT 
     T.Name
    ,FN.Dates
    ,FN.[Desciption]
FROM
    (SELECT DISTINCT Name FROM Temp) AS T
CROSS APPLY
        [dbo].[FN_GetAccidentDates](T.Name) FN

I hope this will help you.

Good Luck

Upvotes: 1

Related Questions