locked
Help with sql query RRS feed

  • Question

  • Hi community,

    Please help me to write a query to find animals vaccination dates in this format ( see Result). Vaccination records (DA2PP for Dogs and FVRCP for Cats ) given in various dates are shown at the bottom (see Data).

    Appreciate your help.

    Result:
    AnimalId VACType Vaccination1 Vaccination2 Vaccination3 Vaccination4
    D1403310 DA2PP 5/2/2016 4/2/2019 NULL NULL
    C1717260 FVRCP 8/15/2017 3/12/2019 NULL NULL
    C1717770 FVRCP 3/14/2019 3/29/2019 NULL NULL
    C1718480 FVRCP 3/16/2019 3/30/2019 NULL NULL
    C1718870 FVRCP 3/19/2019 4/2/2019 NULL NULL
    C1721210 FVRCP 3/26/2019 NULL NULL NULL
    D1721730 DA2PP 5/25/2018 6/12/2018 11/9/2018 3/28/2019
    D1721830 DA2PP 3/28/2019 NULL NULL NULL
    D1722810 DA2PP 4/1/2019 NULL NULL NULL
    D1723120 DA2PP 4/2/2019 NULL NULL NULL
    D1852130 NULL NULL NULL NULL

    NULL

    Data:

    AnimalId VACType AnimalType Date
    D1403310 DA2PP DOG 5/2/2016
    D1403310 DA2PP DOG 4/2/2019
    C1717260 FVRCP CAT 8/15/2017
    C1717260 FVRCP CAT 3/12/2019
    C1717770 FVRCP CAT 3/14/2019
    C1717770 FVRCP CAT 3/29/2019
    C1718480 FVRCP CAT 3/16/2019
    C1718480 FVRCP CAT 3/30/2019
    C1718870 FVRCP CAT 3/19/2019
    C1718870 FVRCP CAT 4/2/2019
    C1721210 FVRCP CAT 3/26/2019
    D1721730 DA2PP DOG 5/25/2018
    D1721730 DA2PP DOG 6/12/2018
    D1721730 DA2PP DOG 11/9/2018
    D1721730 DA2PP DOG 3/28/2019
    D1721830 DA2PP DOG 3/28/2019
    D1722810 DA2PP DOG 4/1/2019
    D1723120 DA2PP DOG 4/2/2019
    D1852130 NULL DOG NULL

    Monday, January 6, 2020 4:42 AM

Answers

  • Try this? (Please share DML while posting a question, it would really help us to help you better)

    create Table Vaccinations (AnimalId	char(8),VACType char(5),AnimalType char(10),Date date)
    Insert into Vaccinations
    Values
    ('D1403310','DA2PP','DOG','5/2/2016'),
    ('D1403310','DA2PP','DOG','4/2/2019'),
    ('C1717260','FVRCP','CAT','8/15/2017'),
    ('C1717260','FVRCP','CAT','3/12/2019'),
    ('C1717770','FVRCP','CAT','3/14/2019'),
    ('C1717770','FVRCP','CAT','3/29/2019'),
    ('C1718480','FVRCP','CAT','3/16/2019'),
    ('C1718480','FVRCP','CAT','3/30/2019'),
    ('C1718870','FVRCP','CAT','3/19/2019'),
    ('C1718870','FVRCP','CAT','4/2/2019'),
    ('C1721210','FVRCP','CAT','3/26/2019'),
    ('D1721730','DA2PP','DOG','5/25/2018'),
    ('D1721730','DA2PP','DOG','6/12/2018'),
    ('D1721730','DA2PP','DOG','11/9/2018'),
    ('D1721730','DA2PP','DOG','3/28/2019'),
    ('D1721830','DA2PP','DOG','3/28/2019'),
    ('D1722810','DA2PP','DOG','4/1/2019'),
    ('D1723120','DA2PP','DOG','4/2/2019'),
    ('D1852130',NULL,'DOG',	NULL)
    
    ;With cte as (Select *, Row_number()Over(partition by AnimalId,VACType order by Date asc) Rn From Vaccinations)
    Select AnimalId,VACType, 
    	Max(Case when Rn=1 Then date else NULL End) 'Vaccinations1',
    	Max(Case when Rn=2 Then date else NULL End) 'Vaccinations2',
    	Max(Case when Rn=3 Then date else NULL End) 'Vaccinations3',
    	Max(Case when Rn=4 Then date else NULL End) 'Vaccinations4' 
    From cte Group by AnimalId,VACType

    OR 

    With PIVOT:

    ;With cte as (Select *, Row_number()Over(partition by AnimalId,VACType order by Date asc) Rn From Vaccinations)
    Select AnimalId,VACType,[1] 'Vaccinations1',[2] 'Vaccinations2',[3] 'Vaccinations3',[4] 'Vaccinations4' 
    From (Select AnimalId,VACType,Rn, cast(date as varchar(max)) sdate  From cte)t
    PIVOT
    (MAX(sdate) FOR rn in ([1],[2],[3],[4])) as PivotTables


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]


    • Edited by SQLZealots Monday, January 6, 2020 5:09 AM
    • Proposed as answer by Naomi N Monday, January 6, 2020 1:41 PM
    • Unproposed as answer by Naomi N Monday, January 6, 2020 1:42 PM
    • Proposed as answer by Naomi N Monday, January 6, 2020 1:42 PM
    • Marked as answer by Anita5523 Monday, January 6, 2020 2:33 PM
    Monday, January 6, 2020 4:58 AM

All replies

  • Try this? (Please share DML while posting a question, it would really help us to help you better)

    create Table Vaccinations (AnimalId	char(8),VACType char(5),AnimalType char(10),Date date)
    Insert into Vaccinations
    Values
    ('D1403310','DA2PP','DOG','5/2/2016'),
    ('D1403310','DA2PP','DOG','4/2/2019'),
    ('C1717260','FVRCP','CAT','8/15/2017'),
    ('C1717260','FVRCP','CAT','3/12/2019'),
    ('C1717770','FVRCP','CAT','3/14/2019'),
    ('C1717770','FVRCP','CAT','3/29/2019'),
    ('C1718480','FVRCP','CAT','3/16/2019'),
    ('C1718480','FVRCP','CAT','3/30/2019'),
    ('C1718870','FVRCP','CAT','3/19/2019'),
    ('C1718870','FVRCP','CAT','4/2/2019'),
    ('C1721210','FVRCP','CAT','3/26/2019'),
    ('D1721730','DA2PP','DOG','5/25/2018'),
    ('D1721730','DA2PP','DOG','6/12/2018'),
    ('D1721730','DA2PP','DOG','11/9/2018'),
    ('D1721730','DA2PP','DOG','3/28/2019'),
    ('D1721830','DA2PP','DOG','3/28/2019'),
    ('D1722810','DA2PP','DOG','4/1/2019'),
    ('D1723120','DA2PP','DOG','4/2/2019'),
    ('D1852130',NULL,'DOG',	NULL)
    
    ;With cte as (Select *, Row_number()Over(partition by AnimalId,VACType order by Date asc) Rn From Vaccinations)
    Select AnimalId,VACType, 
    	Max(Case when Rn=1 Then date else NULL End) 'Vaccinations1',
    	Max(Case when Rn=2 Then date else NULL End) 'Vaccinations2',
    	Max(Case when Rn=3 Then date else NULL End) 'Vaccinations3',
    	Max(Case when Rn=4 Then date else NULL End) 'Vaccinations4' 
    From cte Group by AnimalId,VACType

    OR 

    With PIVOT:

    ;With cte as (Select *, Row_number()Over(partition by AnimalId,VACType order by Date asc) Rn From Vaccinations)
    Select AnimalId,VACType,[1] 'Vaccinations1',[2] 'Vaccinations2',[3] 'Vaccinations3',[4] 'Vaccinations4' 
    From (Select AnimalId,VACType,Rn, cast(date as varchar(max)) sdate  From cte)t
    PIVOT
    (MAX(sdate) FOR rn in ([1],[2],[3],[4])) as PivotTables


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]


    • Edited by SQLZealots Monday, January 6, 2020 5:09 AM
    • Proposed as answer by Naomi N Monday, January 6, 2020 1:41 PM
    • Unproposed as answer by Naomi N Monday, January 6, 2020 1:42 PM
    • Proposed as answer by Naomi N Monday, January 6, 2020 1:42 PM
    • Marked as answer by Anita5523 Monday, January 6, 2020 2:33 PM
    Monday, January 6, 2020 4:58 AM
  • Hi Anita5523,

    Or please try dynamic script . 

    IF OBJECT_ID('Vaccinations') IS NOT NULL drop table  Vaccinations   
    go 
    create Table Vaccinations (AnimalId	char(8),VACType char(5),AnimalType char(10),Date date)
    Insert into Vaccinations
    Values
    ('D1403310','DA2PP','DOG','5/2/2016'),
    ('D1403310','DA2PP','DOG','4/2/2019'),
    ('C1717260','FVRCP','CAT','8/15/2017'),
    ('C1717260','FVRCP','CAT','3/12/2019'),
    ('C1717770','FVRCP','CAT','3/14/2019'),
    ('C1717770','FVRCP','CAT','3/29/2019'),
    ('C1718480','FVRCP','CAT','3/16/2019'),
    ('C1718480','FVRCP','CAT','3/30/2019'),
    ('C1718870','FVRCP','CAT','3/19/2019'),
    ('C1718870','FVRCP','CAT','4/2/2019'),
    ('C1721210','FVRCP','CAT','3/26/2019'),
    ('D1721730','DA2PP','DOG','5/25/2018'),
    ('D1721730','DA2PP','DOG','6/12/2018'),
    ('D1721730','DA2PP','DOG','11/9/2018'),
    ('D1721730','DA2PP','DOG','3/28/2019'),
    ('D1721830','DA2PP','DOG','3/28/2019'),
    ('D1722810','DA2PP','DOG','4/1/2019'),
    ('D1723120','DA2PP','DOG','4/2/2019'),
    ('D1852130',NULL,'DOG',	NULL)
    
    DECLARE @sql_str VARCHAR(8000)
    DECLARE @sql_col VARCHAR(8000)
    DECLARE @sql_col1 VARCHAR(8000)
    SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(rn) FROM (Select distinct Row_number()Over(partition by AnimalId,VACType order by Date asc) Rn From Vaccinations)t 
    SELECT @sql_col1 = ISNULL(@sql_col1 + ',','') + QUOTENAME(rn)+' Vaccinations'+cast(rn as varchar(20)) FROM (Select distinct Row_number()Over(partition by AnimalId,VACType order by Date asc) Rn From Vaccinations)t 
    SET @sql_str = '
    ;With cte as (Select *, Row_number()Over(partition by AnimalId,VACType order by Date asc) Rn From Vaccinations)
    Select AnimalId,VACType,'+@sql_col1+' 
    From (Select AnimalId,VACType,Rn, cast(date as varchar(max)) sdate  From cte)t
    PIVOT
    (MAX(sdate) FOR rn in ('+@sql_col+')) as PivotTables'
    PRINT (@sql_str)
    EXEC (@sql_str)
    /*
    ;With cte as (Select *, Row_number()Over(partition by AnimalId,VACType order by Date asc) Rn From Vaccinations)
    Select AnimalId,VACType,[1] Vaccinations1,[2] Vaccinations2,[3] Vaccinations3,[4] Vaccinations4 
    From (Select AnimalId,VACType,Rn, cast(date as varchar(max)) sdate  From cte)t
    PIVOT
    (MAX(sdate) FOR rn in ([1],[2],[3],[4])) as PivotTables
    AnimalId VACType Vaccinations1                                                                                                                                                                                                                                                    Vaccinations2                                                                                                                                                                                                                                                    Vaccinations3                                                                                                                                                                                                                                                    Vaccinations4
    -------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    D1852130 NULL    NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL
    D1403310 DA2PP   2016-05-02                                                                                                                                                                                                                                                       2019-04-02                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL
    D1721730 DA2PP   2018-05-25                                                                                                                                                                                                                                                       2018-06-12                                                                                                                                                                                                                                                       2018-11-09                                                                                                                                                                                                                                                       2019-03-28
    D1721830 DA2PP   2019-03-28                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL
    D1722810 DA2PP   2019-04-01                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL
    D1723120 DA2PP   2019-04-02                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL
    C1717260 FVRCP   2017-08-15                                                                                                                                                                                                                                                       2019-03-12                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL
    C1717770 FVRCP   2019-03-14                                                                                                                                                                                                                                                       2019-03-29                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL
    C1718480 FVRCP   2019-03-16                                                                                                                                                                                                                                                       2019-03-30                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL
    C1718870 FVRCP   2019-03-19                                                                                                                                                                                                                                                       2019-04-02                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL
    C1721210 FVRCP   2019-03-26                                                                                                                                                                                                                                                       NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL
    
    */
    
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 6, 2020 8:42 AM
  • That is very bad form, because what happens if an animal has >4 vaccinations?  Do you expect there to be a 5, 6, 7, 8... columns?  If so, that is difficult to do in standard TSQL.

    If you only want the most current 4, that is easy.

    Monday, January 6, 2020 12:59 PM
  • If the number of vaccinations is a variable, try the dynamic script:

    DECLARE @maxNumberOfColumns int = 0;
    DECLARE @index int = 1;
    DECLARE @columnList varchar(1000) = '';
    DECLARE @sql varchar(max) = '';
    
    SELECT @maxNumberOfColumns = MAX(VaccinationNumber)
    FROM (
    	SELECT COUNT([Date]) AS VaccinationNumber
    	FROM VaccinationRecords
    	WHERE [Date] IS NOT NULL
    	GROUP BY [AnimalId]
    ) AS t;
    
    WHILE @index <= @maxNumberOfColumns
    BEGIN
    	IF @columnList = ''
    	BEGIN
    		SET @columnList = @columnList + QUOTENAME('Vaccination' + CAST(@index AS varchar(3)));
    	END
    	ELSE
    	BEGIN
    		SET @columnList = @columnList + ', ' + QUOTENAME('Vaccination' + CAST(@index AS varchar(3)));
    	END
    
    	SET @index = @index + 1;
    END
    
    SET @sql = N'
    SELECT [AnimalId], [VACType], ' + @columnList + '
    FROM (
    	SELECT *, ''Vaccination'' + CAST(ROW_NUMBER() OVER(PARTITION BY [AnimalId], [VACType] ORDER BY [Date]) AS varchar(3)) AS RowNumber
    	FROM VaccinationRecords 
    ) AS src
    PIVOT (
    	MAX([Date]) For RowNumber IN (' + @columnList + ')
    ) AS pvt;
    ';
    
    PRINT(@sql);
    EXEC(@sql);


    A Fan of SSIS, SSRS and SSAS

    Monday, January 6, 2020 3:14 PM
  • >> Please help me to write a query to find animals vaccination dates in this format ( see Result). <<

    No! You failed to post any DDL so we have no idea what the data looks like. You expect us to come up with an entire database schema from an ASCII character picture in a posting. If you're working for somebody that makes you program like this, quit. 

    It also looks like you flocked up on the date formats; the only format allowed for ANSI/ISO standard SQL is based on the ISO 8601 standard (yyyy-mm-dd) and not the ambiguous format you published.

    The real problem is that you don't of the difference between doing a query and writing a report. Please trust me, we never intended SQL to be used for report writing.

    After that, you don't seem to know what normalization is. Please look at the definition of first normal form (1NF) and what a "repeated group" means. Let me start by making a guess at what you should have posted.

    CREATE TABLE Vaccination_History 
    (animal_id  CHAR(8) NOT NULL
      CHECK(animal_id LIKE '[CD][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    vacination_name CHAR(5) NOT NULL
     REFERENCES Vaccination_Schedule (vacination_name),
    vacination_date DATE NOT NULL,
     ..);

    CREATE TABLE Vaccination_Schedule 
    (vacination_name CHAR(5) NOT NULL PRIMARY KEY,
      CHECK (vacination_name IN (..) ),
     vacination_frequency INTEGER DEFAULT 1 NOT NULL 
      CHECK(vacination_frequency > 0),
    ..);

    I'm kind of bothered by putting the species of the pet in the identifier, instead of making it a separate column. However, I think we need a vaccination schedule table and I'm not quite sure what it has to look like. I've made the simple assumption, since I don't have any specs, that all we need to know is the frequency; some vaccinations are a series of one, two, or three shots or whatever, and maybe an "as needed" code. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, January 6, 2020 8:27 PM