July 20, 2009 at 2:35 pm
I have 3 tables in my system.
Table1: AvailableServices
----------------------------------
AvailableServicesIDServiceNameServiceDescription
1000Basic This is the basic plan for my product
1001Intermediate This is the intermediate plan for my product
1002Premium This is the premium plan for my product
Table2: AvailableServiceAttributes
----------------------------------
AvailableServiceAttributesIDAttribute
1000 CreateCustomers
1001 CreateUsers
1002 CreateReports
Table3: AvailableServiceDetails
----------------------------------
AvailableServiceDetailsIDAvailableServicesIDAvailableServiceAttributesIDValue
1000100010001
10031001100010
10041001100110
100610021000100
100710021001100
100810021002100
I need the result set as follows:
Attribute BasicIntermediatePremium
CreateCustomers110100
CreateUsersX10100
CreateReportsXX100
Please help me out as I am in new to Pivoting......
July 20, 2009 at 3:45 pm
Hi,
below please find two version on how to get the pivot done:
The first one is using "cross tabs" and the second one the SQL PIVOT syntax.
Usually, the cross tab function is faster.
For details on cross tabs and pivoting please see Jeff's cross tab article, Part 1[/url] and if you'll need dynamic cross tabs I'd recommend Jeff's cross tab article, Part 2[/url].
Note: Please have a look at the article in my signature as well as the way the sample data are included in the code below.
Usually, we'd expect you to provide sample data in a ready to use format. This will also speed up answers in most cases.
Consider it as a "Welcome-To-SSC-giveaway" 😉
-- create sample data
DECLARE @as TABLE (ASID INT , ServiceName VARCHAR (50), ServiceDescription VARCHAR (200))
INSERT INTO @as
SELECT 1000 ,'Basic',' This is the basic plan for my product' UNION ALL
SELECT 1001 ,'Intermediate',' This is the intermediate plan for my product' UNION ALL
SELECT 1002 ,'Premium',' This is the premium plan for my product'
DECLARE @ASA TABLE (ASAID INT , Attribute VARCHAR (50))
INSERT INTO @ASA
SELECT 1000 ,'CreateCustomers' UNION ALL
SELECT 1001 ,'CreateUsers' UNION ALL
SELECT 1002 ,'CreateReports'
DECLARE @asd TABLE (ASDID INT ,ASID INT ,ASAID INT ,Value INT )
INSERT INTO @asd
SELECT 1000 ,1000, 1000 ,1 UNION ALL
SELECT 1003 ,1001 ,1000 ,10 UNION ALL
SELECT 1004 ,1001 ,1001 ,10 UNION ALL
SELECT 1006 ,1002 ,1000, 100 UNION ALL
SELECT 1007, 1002, 1001, 100 UNION ALL
SELECT 1008, 1002, 1002, 100
-- cross tab solution
SELECT asa.Attribute,
ISNULL(CAST(MAX(CASE WHEN a.servicename='Basic' THEN asd.value END) AS VARCHAR(5)),'X') AS Basic,
ISNULL(CAST(MAX(CASE WHEN a.servicename='Intermediate' THEN asd.value END) AS VARCHAR(5)),'X') AS Intermediate,
ISNULL(CAST(MAX(CASE WHEN a.servicename='Premium' THEN asd.value END) AS VARCHAR(5)),'X') AS Premium
FROM @ASA asa
INNER JOIN @asd asd ON asd.ASAID = asa.ASAID
INNER JOIN @as a ON asd.asid = a.ASID
GROUP BY asa.Attribute,asa.ASAID
ORDER BY asa.ASAID
-- pivot solution
SELECT Attribute,
ISNULL(CAST([Basic]AS VARCHAR(5)),'X') AS Basic,
ISNULL(CAST([Intermediate]AS VARCHAR(5)),'X') AS Intermediate,
ISNULL(CAST([Premium]AS VARCHAR(5)),'X') AS Premium
FROM
(SELECT asa.Attribute AS Attribute ,asa.ASAID, a.ServiceName AS ServiceName, asd.value AS value
FROM @ASA asa
INNER JOIN @asd asd ON asd.ASAID = asa.ASAID
INNER JOIN @as a ON asd.asid = a.ASID
) p
PIVOT
(
MAX (value)
FOR ServiceName IN
( [Basic], [Intermediate], [Premium] )
) AS pvt
ORDER BY ASAID
/* result set for both version
AttributeBasicIntermediatePremium
CreateCustomers110100
CreateUsersX10100
CreateReportsXX100*/
July 20, 2009 at 4:07 pm
Hi Lutz,
Thanks for the reply and the "give away" stuff. 😉
This is what I wanted. But having said that, is there any way that we can avoid hard coding 'Basic', Intermediate' and 'Advanced'. In future there may be more plans added and they may be editing the existing plan names.
Thanks,
Sharmin
July 20, 2009 at 4:17 pm
sharminjose (7/20/2009)
...But having said that, is there any way that we can avoid hard coding 'Basic', Intermediate' and 'Advanced'. In future there may be more plans added and they may be editing the existing plan names.Thanks,
Sharmin
Hi Sharmin,
it seems like you're looking for some dynamic cross tab...
I'd recommend to read the second article I referred to in my previous post.
If you have difficulties to apply it to your scenario get back to this forum/thread and I'm sure we can help you to get it resolved.
July 28, 2009 at 10:47 am
Hi Lutz,
Sorry for the delay in replying. I was a bit busy with the production tickets.
I found the solution. Thanks for all the helps.
Here is the query.
DECLARE @SQL1 VARCHAR(MAX),@SQL2 VARCHAR(MAX)='';
SET @SQL1 = 'SELECT asa.Attribute as ''Quick View'','+CHAR(10);
SELECT @SQL2 = @SQL2 + ' ISNULL(CAST(MAX(CASE WHEN a.servicename='''+ServiceName+''' THEN asd.value END) AS VARCHAR(5)),''No'') AS ['+ServiceName+'],' FROM AvailableServices;
SET @SQL2=SUBSTRING(@SQL2,0,LEN(@SQL2));
SET @SQL2 = @SQL2 +' FROM AvailableServiceAttributes asa
INNER JOIN AvailableServiceDetails asd ON asd.AvailableServiceAttributesID = asa.AvailableServiceAttributesID
INNER JOIN AvailableServices a ON asd.AvailableServicesID = a.AvailableServicesID
GROUP BY asa.Attribute,asa.AvailableServiceAttributesID
ORDER BY asa.AvailableServiceAttributesID';
EXEC(@SQL1+@SQL2);
Once again, thank you so much for your helps....
Regards,
Sharmin
July 28, 2009 at 1:13 pm
Sharmin Jose (7/28/2009)
Once again, thank you so much for your helps....
Well, there wasn't that much I did other than posting a link. 😉 You did the major stuff all by yourself! Good job!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply