convert standard row by row to horizontally arrange records (complex)

BillAn1,

Query created. Amazing !! it worked !!

by the way i've place distinct from the select part, i'm not sure if this is correct since data was multiplying.
i.e.

select DISTINCT dance.tableID, sing.tableID,eat.tableID, dance.sesionID , sing.sessionID, eat.sessionID, dance.contestantID, dance.score, sing.score, eat.score
from


unfortunately the speed was terrible 30 seconds when we join 6 categories, there are 11 total. (took 3 minutes)

Tried to join upto 5 category it only took 1 second to finish the query, kindly advice how can we speed up the query.

Data is only test value for now(less than 50 records), the expected raw data will be 10,000 records per week.



Thanks.

Can you please post the SQL statement that you are trying to run that is slow.

can you double check your queries. Often if your data is "multiplying" there is some missing join clause, that a DISTICT will mask. By adding the DISTINCt it will remove the duplicates, but it will still result in  ahuge intermediate dataset, which will result in data perofrmance issues.

Can you check that each of the inner nested queries returns only one row per contestant ID. If not, chekc htat youare not missing a where clause or a join clause in the inner query.

Also, check that you have indexes on the appropriate columns, particularly on the b.categoryDescription column.

If you are not getting anywhere, can youpost the strufture of your tables a,b,c and hte actual query you run.

BillAn1,

Please see below for the sql query, we have only queried two columns for now and did 11 joins.

Kindly assist where the missing link is.

Thanks.

select distinct
delivery.operatorid as operatorid,
delivery.operatorscoreid as deliveryID  
from
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'delivery'
) delivery
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'listening skill'
) listeningskill on delivery.operatorid=listeningskill.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'communication skill'
) communicationskill on delivery.operatorid=communicationskill.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'opening'
) opening on delivery.operatorid=opening.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'searching'
) searching on delivery.operatorid=searching.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'verification'
) verification on delivery.operatorid=verification.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'providing information'
) providinginformation on delivery.operatorid=providinginformation.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'closing / transfer'
) closingtransfer on delivery.operatorid=closingtransfer.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'timely'
) timely on delivery.operatorid=timely.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'customer service'
) customerservice on delivery.operatorid=customerservice.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'type of search'
) typeofsearch on delivery.operatorid=typeofsearch.operatorid
join
(
select b.operatorscoreID,a.operatorid,b.score from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'transfer required'
) transferrequired on delivery.operatorid=transferrequired.operatorid

It's difficult without knowing th e data etc to determine where (if) the problem is regarding the sub query.

can you check each of the nested queries,

select count(*)  from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'delivery'

etc, and determine how many rows they return, also can you check for each of them how many distinct rows :

select count(distinct a.operatorID)  from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'delivery'

if these 2 numbers are different, then you have a problem with multiple scores per operator.

Also, you may want to change your queries from "left join"  ti simple "join". since there is a conditionla clause on the subcategorydescription, the left join has no effect on the dataset returned, and it may result in slower performance.

There are total of 87 records found for break down is one operator with 6 set of scores per category group (scoreID)

select b.operatorscoreID,a.operatorid,b.score,b.scoreid from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid

Remove all the left join and replace with join, sadly no improvement on the query time, 3 minutes

hope this help us in identifying the culprit.

output:
ID            operatorID score scoreID
85      1001      1      6
86      1001      1      6
87      1001      1      6
88      1001      5      6
89      1001      5      6
90      1001      1      6
91      1001      5      6
92      1001      5      6
93      1001      3      6
94      1001      3      6
95      1001      5      6
96      1001      N      6
97      1001      3      7
98      1001      4      7
99      1001      4      7
100      1001      5      7
101      1001      5      7
102      1001      5      7
103      1001      5      7
104      1001      5      7
105      1001      4      7
106      1001      3      7
107      1001      5      7
108      1001      N      7
109      1001      3      8
110      1001      4      8
111      1001      4      8
112      1001      5      8
113      1001      5      8
114      1001      5      8
115      1001      5      8
116      1001      5      8
117      1001      4      8
118      1001      3      8
119      1001      5      8
120      1001      N      8
121      1001      3      9
122      1001      4      9
123      1001      4      9
124      1001      5      9
125      1001      5      9
126      1001      5      9
127      1001      5      9
128      1001      5      9
129      1001      4      9
130      1001      3      9
131      1001      5      9
132      1001      N      9
133      1001      3      10
134      1001      4      10
135      1001      4      10
136      1001      5      10
137      1001      5      10
138      1001      5      10
139      1001      5      10
140      1001      5      10
141      1001      4      10
142      1001      3      10
143      1001      5      10
144      1001      N      10
145      1001      3      11
146      1001      4      11
147      1001      4      11
148      1001      5      11
149      1001      5      11
150      1001      5      11
151      1001      5      11
152      1001      5      11
153      1001      4      11
154      1001      3      11
155      1001      5      11
156      1001      N      11
157      1001      3      12
158      1001      4      12
159      1001      4      12
160      1001      5      12
161      1001      5      12
162      1001      5      12
163      1001      5      12
164      1001      5      12
165      1001      4      12
166      1001      3      12
167      1001      5      12
168      1001      N      12

The resultss return same for all categories. 7 for count and 1 for distinct

Returned -7 -
select count(*)  from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'delivery'

Returned -1 -
select count(distinct a.operatorID)  from operator_analysis a
left join operator_score b on a.scoreid = b.scoreid
left join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'delivery'

Kindly see below for table structures.



CREATE TABLE [subcategory] (
      [subcategoryid] [int] IDENTITY (1, 1) NOT NULL ,
      [maincategoryid] [int] NULL ,
      [subCategorydescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      CONSTRAINT [PK__subcategory__57DDD73B] PRIMARY KEY  CLUSTERED
      (
            [subcategoryid]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [operator_analysis] (
      [operatoranalysisid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
      [OperatorID] [numeric](18, 0) NULL ,
      [ScoreID] [numeric](10, 0) NULL
      CONSTRAINT [PK__operator_analysi__503CB573] PRIMARY KEY  CLUSTERED
      (
            [operatoranalysisid]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [operator_score] (
      [operatorscoreID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,    
      [scoreid] [int] NOT NULL ,          
      [mainCategoryid] [int] NULL ,
      [subcategoryid] [int] NULL ,
      [score] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,   <----------actual scores
      CONSTRAINT [PK_operator_score] PRIMARY KEY  CLUSTERED
      (
            [operatorscoreID]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO

from your point you say there are 7 records, but only 1 when you do a distinct. This tells me that the one operator has 7 differnet scores for sub category 'delivery' etc? Is it correct that the one operator has 7 diff scores, or is there a problem with duplicate data?

I can't understand your data well enough yet. Is it possible to give me the results to the following

select * from operator_analysis
select * from operator_score

BillAn1,

That is correct there is only 1 operator with 7 different scores

operator_analysis contains the employeeID and the scoreID
i.e.
operatoranalysisid  opeartorid                    scoreID
12                    1001            6
13                    1001            7
14                    1001            8
15                    1001            9
16                    1001            10
17                    1001            11
18                    1001            12

operator_score contains the scoreID and actual scores

OperatorscoreID  scoreID mainCatID SubCatID Score
85      6      1      1      1
86      6      1      2      1
87      6      1      3      1
88      6      2      4      5
89      6      2      5      5
90      6      2      12      1
91      6      2      6      5
92      6      2      7      5
93      6      3      8      3
94      6      3      9      3
95      6      4      10      5
96      6      4      11      N
97      7      1      1      3
98      7      1      2      4
99      7      1      3      4
100      7      2      4      5
101      7      2      5      5
102      7      2      12      5
103      7      2      6      5
104      7      2      7      5
105      7      3      8      4
106      7      3      9      3
107      7      4      10      5
108      7      4      11      N
109      8      1      1      3
110      8      1      2      4
111      8      1      3      4
112      8      2      4      5
113      8      2      5      5
114      8      2      12      5
115      8      2      6      5
116      8      2      7      5
117      8      3      8      4
118      8      3      9      3
119      8      4      10      5
120      8      4      11      N
121      9      1      1      3
122      9      1      2      4
123      9      1      3      4
124      9      2      4      5
125      9      2      5      5
126      9      2      12      5
127      9      2      6      5
128      9      2      7      5
129      9      3      8      4
130      9      3      9      3
131      9      4      10      5
132      9      4      11      N
133      10      1      1      3
134      10      1      2      4
135      10      1      3      4
136      10      2      4      5
137      10      2      5      5
138      10      2      12      5
139      10      2      6      5
140      10      2      7      5
141      10      3      8      4
142      10      3      9      3
143      10      4      10      5
144      10      4      11      N
145      11      1      1      3
146      11      1      2      4
147      11      1      3      4
148      11      2      4      5
149      11      2      5      5
150      11      2      12      5
151      11      2      6      5
152      11      2      7      5
153      11      3      8      4
154      11      3      9      3
155      11      4      10      5
156      11      4      11      N
157      12      1      1      3
158      12      1      2      4
159      12      1      3      4
160      12      2      4      5
161      12      2      5      5
162      12      2      12      5
163      12      2      6      5
164      12      2      7      5
165      12      3      8      4
166      12      3      9      3
167      12      4      10      5
168      12      4      11      N



Thanks.

Somewhere along the line we have swapped from talking about contestantID to OperatorID etc - is this of significance? In your original examplesyou showed contestantID 1001 had one set of scores, i.e. one score for each of the subcategories. Now it would appear that the one operatorID has 7 different scores for the same subcategory.
Can you explain for these different scores what the final output would look like? should there be 7 rows for a given operatorID, or just one? if 1, then what score should be used etc?

BillAn1,

Your simply amazing !!!

Thanks so much should have been scoreid

select b.operatorscoreID,a.operatorid,b.score, a.scoreid from operator_analysis a
join operator_score b on a.scoreid = b.scoreid
join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'transfer required'
) transferrequired on delivery.scoreid=transferrequired.scoreid

When there are multiple operators, will the scoreID be globally unique? or will it be the case that operator 1002 will also have a scoreID of 6....12? If they are globally unique, you're fine. If they repeat themsleves, you need to include both in your join, i.e.

select b.operatorscoreID,a.operatorid,b.score, a.scoreid from operator_analysis a
join operator_score b on a.scoreid = b.scoreid
join subcategory c on c.maincategoryid =b.maincategoryid and c.subcategoryid =b.subcategoryid
where c.subcategorydescription = 'transfer required'
) transferrequired on delivery.scoreid=transferrequired.scoreid and delivery.operatorID=transferrequired.operatorID

BillAn1,

Brilliant idea. Simply the best!!!

Thanks.