Data fetching from table

Question

Hi ,

I have table with list of product columns and its ordered users for specific dates

so I need a sql query to fetch the group the products with specific to each user for the respective given date

For ex:

Product	user	date
Soap	User 1	12-12-2008
Shampoo	User 1	12-12-2008
Tv	User 2	12-12-2008
Stool	User 2	12-12-2008

so I pass the date alone (12/12-2008)-- it should group the products according to user and show

Thanks

Pankaj Patel · Answer

DECLARE @Orders TABLE ( OrderId int NOT NULL, UserName varchar(20), ProductName varchar(20), OrderDate datetime ) INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (1, 'User1', 'Soap', '2008-10-10') INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (2, 'User1', 'Shampoo', '2008-10-10') INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (3, 'User2', 'Tv', '2008-10-10') INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (4, 'User2', 'Stool', '2008-10-10') INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (5, 'User1', 'Soap', '2008-12-12') INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (6, 'User1', 'Shampoo', '2008-12-12') INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (7, 'User2', 'Tv', '2008-12-12') INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (8, 'User2', 'Stool', '2008-12-12') declare @date datetime = '2008-12-12' ; with cte_data as ( select ROW_NUMBER() over(partition by UserName order by UserName, OrderDate ) as SrNo, UserName, ProductName from @Orders where OrderDate = @date) select case when d.SrNo = 1 then d.UserName else '' end UserName, d.ProductName from cte_data as d Output UserName ProductName User 1 Soap Shampoo User 2 Tv Stool Hope, this will help you!

Pankaj Patel · Answer

Hi Sathish, You can fetch desired format data from table as following way: DECLARE @Users TABLE ( UserId int NOT NULL, UserName varchar(20) ) DECLARE @Products TABLE ( ProductId int NOT NULL, ProductName varchar(20) ) DECLARE @Orders TABLE ( OrderId int NOT NULL, UserId int NOT NULL, ProductId int NOT NULL, OrderDate datetime ) INSERT INTO @Users (UserId, UserName) VALUES (1, 'User 1') INSERT INTO @Users (UserId, UserName) VALUES (2, 'User 2') INSERT INTO @Products (ProductId, ProductName) VALUES (1, 'Soap') INSERT INTO @Products (ProductId, ProductName) VALUES (2, 'Shampoo') INSERT INTO @Products (ProductId, ProductName) VALUES (3, 'TV') INSERT INTO @Products (ProductId, ProductName) VALUES (4, 'Stool') INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (1, 1, 1, '2008-12-12') INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (2, 1, 2, '2008-12-12') INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (3, 2, 3, '2008-12-12') INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (4, 2, 4, '2008-12-12') INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (5, 1, 1, '2008-10-10') INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (6, 1, 2, '2008-10-10') INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (7, 2, 3, '2008-10-10') INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (8, 2, 4, '2008-10-10') declare @date datetime = '2008-12-12' SELECT p.ProductName, u.UserName, CONVERT(varchar, o.OrderDate, 103) as OrderDate FROM @Orders o inner join @Products p on p.ProductId = o.ProductId inner join @Users u on u.UserId = o.UserId where o.OrderDate = @date Output : ProductName UserName OrderDate Soap User 1 12/12/2008 Shampoo User 1 12/12/2008 TV User 2 12/12/2008 Stool User 2 12/12/2008 Hope, this will help you!

sathish · Answer

Thank you Pankaj Patel

sathish · Answer

Thanks Patel. But already I have the table like this, I need to fetch records as below, when pass on the date in where condition User Product User1 Soap Shampoo User2 Tv Stool Thanks