CODEWITHSUNDEEP
sqlsql-server
Kaza Avinash
Kaza Avinash

Reputation: 21

How to show date column in horizontal format in sql

I have two columns in my Product table :-

  Product          |       Date
--------------------------------------
     Prod1       |     06/01/2018
     Prod2       |     06/03/2018
     Prod3       |     06/05/2018

I want the output the as :-

 Product | 06/01/2018 | 06/02/2018 | 06/03/2018| 06/04/2018 | 06/05/2018
-------------------------------------------------------------------------
     Prod1 |     X           -            -           -            -
     Prod2 |     -           -            X           -            -
     Prod3 |     -           -            -           -            X

I want to display this table in a grid. Is this possible by writing a query in sql ?

Upvotes: 2

Views: 1342

Answers (2)

LukStorms
LukStorms

Reputation: 29657

Here's an example that also uses Dynamic SQL with a PIVOT.

Except that how the column names are generates is done slightly different.

And sp_executesql is used instead of just EXEC. 

--
-- Using a temporary table for testing reasons
--
if object_id('tempdb..#Test_Products')IS NOT NULL DROP TABLE #Test_Products;
create table #Test_Products (Product varchar(30), [Date] date);

--
-- Sample data
--
insert into #Test_Products (Product, [Date]) values
('Prod1', '2018-06-01'),
('Prod2', '2018-06-03'),
('Prod3', '2018-06-05');

--
-- Getting the begindate and enddate. This can also be set to hardcoded values.
--
declare @BeginDate date = (select min([Date]) from #Test_Products);
declare @EndDate date = (select max([Date]) from #Test_Products);

declare @SQL nvarchar(800);
declare @ColNames varchar(500);

--
-- Using a Recursive SQL to get all dates, then use those to get the column names
--
with RCTE as 
(
  select @BeginDate as startdate
  union all
  select dateadd(day,1,startdate)
  from RCTE where startdate < @EndDate
)
select @ColNames = concat(@ColNames+', ', quotename(convert(varchar,startdate,101))) 
from RCTE;

--
-- Generating the Dynamic SQL statement
--
set @SQL = 'select *
from 
(
  select Product, convert(varchar(10),[Date],101) as DateStamp, ''X'' as X
  from #Test_Products
  where [Date] between @BeginDate and @EndDate
  group by Product, [Date]
) src
pivot (
  max(X)
  for DateStamp 
  in ('+ @ColNames +')
) as pvt';

-- select @SQL as SQL;

EXEC sp_executesql @SQL,
        N'@BeginDate date, @EndDate date', 
        @BeginDate = @BeginDate, 
        @EndDate = @EndDate;

Result: 

Product 06/01/2018 06/02/2018 06/03/2018 06/04/2018 06/05/2018
------- ---------- ---------- ---------- ---------- ----------
Prod1   X          NULL       NULL       NULL       NULL
Prod2   NULL       NULL       X          NULL       NULL
Prod3   NULL       NULL       NULL       NULL       X

Upvotes: 0

Sreenu131
Sreenu131

Reputation: 2516

Using Dynamic Sql

IF OBJECT_ID('tempdb..#Temp')IS NOT NULL 
DROP TABLE #Temp
DECLARE @temp AS TABLE (Product varchar(10), [Date] DATE)

INSERT INTO @temp
 SELECT 'Product1',    '06/01/2018' UNION ALL
 SELECT 'Product2',    '06/03/2018' UNION ALL
 SELECT 'Product3',    '06/05/2018' 

DECLARE @StartDate DATE,
        @EndDate DATE      

SELECT @StartDate=MIN([Date])
        ,@EndDate=MAX([Date]) 
FROM @temp

;WITH CTE
AS
(
SELECT DISTINCT Number,
        DATEADD(DAY,Number,@StartDate) AS ReqDays
FROM master.dbo.spt_values WHERE Number BETWEEN 0 AND 100
    AND DATEADD(DAY,Number,@StartDate) BETWEEN @StartDate  AND @EndDate
)
SELECT  Product,
        ReqDays  AS [Date] INTO #Temp 
FROM CTE c
LEFT JOIN @temp t
On c.ReqDays=t.[Date]

DECLARE @Sql nvarchar(max),
        @Col  nvarchar(max),
        @Col2  nvarchar(max)

SELECT @Col=STUFF((SELECT DISTINCT ', '+QUOTENAME([Date]) FROM #Temp FOR XML PATH ('')),1,1,'')
SELECT @Col2=STUFF((SELECT DISTINCT ', '+'ISNULL('+QUOTENAME([Date])+','' '') AS '+QUOTENAME([Date]) FROM #Temp FOR XML PATH ('')),1,1,'')

SET @Sql='
SELECT Product,'+@Col2+'
FROM
(
SELECT *,IIF(DATALENGTH(Product)>0,''X'','''') AS Producttemp FROM #Temp
)AS Src
PIVOT 
(MAX([Producttemp]) FOR [Date] IN ('+@Col+')
)AS PVT WHERE Product IS NOT NULL'
PRINT @Sql
EXEC (@Sql)

Result

Product     2018-06-01  2018-06-02  2018-06-03  2018-06-04  2018-06-05
-------------------------------------------------------------------------
Product1        X                
Product2                                X        
Product3                                                        X

Upvotes: 1

Related Questions