January 14, 2014 at 10:43 am
This is my first forum as i am not able to get a solution
I have a table
C TABLE
Company CodeCompany NameProduct OneProduct TwoProduct Three
101 abc 51 52 55
101 abc 51 52 58
101 abc 51 55 63
102 xyz 52 54 51
102 xyz 51 52 60
103 pqr 53 51 62
i have large table this is just a example
i want to get the cout of the product with company code and company name
like
comanay code | company name | total product 51 |total product 52 |total product 53 |total product 55
101 abc 3 2 0 2
102 xyz 2 2 0 0
like this
i have use the query
select [company code],[company name], count(*) from ctable where (product one='51' or product two='51' or product three='51') and (compnay code='101') group by c[company code],[company name]
but i get only one colum product one not others
so i used other query
select (select count(*) from ctable where (product one='51' or product two='51' or product three='51') and (compnay code='101'),
select (select count(*) from ctable where (product one='52' or product two='52' or product three='52') and (compnay code='101'),
select (select count(*) from ctable where (product one='53' or product two='53' or product three='53') and (compnay code='101') )
so on but this one not give the company code and company name and also it will take huge time and so many codes
to get the desired data as i have large data around 100000 rows so i need solution very urgently
i have used the while loop for the first query where i get the data for one colum not for other so kindly help me as soon as possible.......
January 14, 2014 at 11:19 am
Welcome to the forums!
You have a nice problem here that will help you to learn a lot.
First of all, it's considered "best practices" to post DDL and sample data in a consumable form to help us get directly into the problem instead of working on generating sample data in our environment. You can read more on how to do it in the article posted on my signature. This time, I did it for you.
CREATE TABLE #SampleData(
CompanyCode int,
CompanyName varchar(10),
ProductOneint,
ProductTwoint,
ProductThreeint) ;
INSERT #SampleData
SELECT
101, 'abc', 51, 52, 55 UNION ALL SELECT
101, 'abc', 51, 52, 58 UNION ALL SELECT
101, 'abc', 51, 55, 63 UNION ALL SELECT
102, 'xyz', 52, 54, 51 UNION ALL SELECT
102, 'xyz', 51, 52, 60 UNION ALL SELECT
103, 'pqr', 53, 51, 62 ;
Next, you have your products in 3 different columns, there are several approaches (UNION ALL, UNPIVOT or CROSS APPLY). I used CROSS APPLY for my solution and you can read about it here: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Now that you have your data normalized, we need to do a cross tab or pivot to arrange it as you need. There are 2 great articles that will explain how to do it.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
Finally, you might notice the construction of variable @SQL2. This method is explained here:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
When you combine all this methods, the solution can come easily with some practice.
DECLARE @SQL1varchar(8000),
@SQL2varchar(8000),
@SQL3varchar(8000);
--Build the header of the query (static fields)
SET @SQL1 = 'SELECT CompanyCode ' + CHAR(13) +
',CompanyName ' + CHAR(13);
-- Build the footer of the query (source)
SELECT @SQL3 = 'FROM(
SELECT CompanyCode,
CompanyName,
Product,
COUNT(*) totalProduct
FROM #SampleData
CROSS APPLY( VALUES(''ProductOne'', ProductOne),
(''ProductTwo'', ProductTwo),
(''ProductThree'', ProductThree))x(Name, Product)
GROUP BY CompanyCode,
CompanyName,
Product)x
GROUP BY CompanyCode,
CompanyName
ORDER BY CompanyCode';
--Build the dynamic part of the query
WITH Products AS(
SELECT Product
FROM #SampleData
CROSS APPLY( VALUES('ProductOne', ProductOne),
('ProductTwo', ProductTwo),
('ProductThree', ProductThree))x(Name, Product)
GROUP BY Product
)
SELECT @SQL2 = (SELECT ',MAX( CASE WHEN Product = ' + CAST(Product AS varchar(10)) +
' THEN totalProduct ELSE 0 END) AS TotalProduct' + CAST(Product AS varchar(10)) +CHAR(13)
FROM Products x
FOR XML PATH(''),TYPE).value('.','varchar(8000)')
--Check the Query built
PRINT @Sql1
+ @Sql2
+ @Sql3
--Execute the query
EXEC(@Sql1 + @Sql2 + @Sql3)
--Clean sample data
DROP TABLE #SampleData
January 14, 2014 at 11:23 am
Be sure to understand all the code, because you'll have to modify it and support it.
If you have any questions, feel free to ask.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply