RadhaKrishnaKiJaya
asked on
SQL question
Hello Experts,
Currently I have a view as below (look Current). I am trying to make it more dynamic, and trying to convert it to as below (look Future). Please let me know how can we make it possible. Thank you in advance.
Thank you!
Current
------------
USE ITN
SELECT DISTINCT
TKT_ID
,CAST(MAX( CASE FIELD_ID WHEN 1 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_1
,CAST(MAX( CASE FIELD_ID WHEN 2 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_2
,CAST(MAX( CASE FIELD_ID WHEN 3 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_3
,CAST(MAX( CASE FIELD_ID WHEN 4 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_4
,CAST(MAX( CASE FIELD_ID WHEN 5 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_5
,CAST(MAX( CASE FIELD_ID WHEN 6 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_6
,CAST(MAX( CASE FIELD_ID WHEN 7 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_7
,CAST(MAX( CASE FIELD_ID WHEN 8 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_8
FROM TKT_DETAILS (nolock)
Future
-------------
USE ITN
SELECT DISTINCT
TKT_ID
,CAST(MAX( CASE FIELD_ID WHEN 1 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_1 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 2 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_2 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 3 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_3 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 4 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_4 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 5 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_5 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 6 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_6 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 7 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_7 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 8 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_8 FROM FIELD_DETAILS
FROM TKT_DETAILS (nolock)
Currently I have a view as below (look Current). I am trying to make it more dynamic, and trying to convert it to as below (look Future). Please let me know how can we make it possible. Thank you in advance.
Thank you!
Current
------------
USE ITN
SELECT DISTINCT
TKT_ID
,CAST(MAX( CASE FIELD_ID WHEN 1 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_1
,CAST(MAX( CASE FIELD_ID WHEN 2 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_2
,CAST(MAX( CASE FIELD_ID WHEN 3 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_3
,CAST(MAX( CASE FIELD_ID WHEN 4 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_4
,CAST(MAX( CASE FIELD_ID WHEN 5 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_5
,CAST(MAX( CASE FIELD_ID WHEN 6 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_6
,CAST(MAX( CASE FIELD_ID WHEN 7 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_7
,CAST(MAX( CASE FIELD_ID WHEN 8 THEN FIELD_VALUE END ) AS VARCHAR(100) ) FIELD_8
FROM TKT_DETAILS (nolock)
Future
-------------
USE ITN
SELECT DISTINCT
TKT_ID
,CAST(MAX( CASE FIELD_ID WHEN 1 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_1 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 2 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_2 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 3 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_3 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 4 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_4 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 5 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_5 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 6 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_6 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 7 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_7 FROM FIELD_DETAILS
,CAST(MAX( CASE FIELD_ID WHEN 8 THEN FIELD_VALUE END ) AS VARCHAR(100) ) SELECT FIELD_8 FROM FIELD_DETAILS
FROM TKT_DETAILS (nolock)
why not to have another mapping table and then just use a single joint and you no need to handle that by writting all logic in your statement.
It looks like you're doing things you should do in the consumer (front-end). This kind of mapping or pivoting should be really not done in the back-end. It smells like an EAV-model. In this case you should review the data model.
Caveat: you now, that NOLOCK can lead to terribly wrong results?
Caveat: you now, that NOLOCK can lead to terribly wrong results?
Just a question, from where the WHEN clause (1,2,3,..8) values comes , Is it needed every these condition should check or may be use WHERE clause if possible.
ASKER
Thank you Sarabhai, I am trying to achieve the goal in any means, it does not matter how. I have two tables, TKT_DETAILS and FIELD_DETAILS. Please see the details below.
TKT_DETAILS
TKT_Id
Field_Id
Field_Value
There are about 750 different fields numbered as 1 thru 750. Each row contains only 1 field. There are 750 rows for every single record vertically.
FIELD_DETAILS
Field_Id
Field_Name
This table contains Field Names of the every Field_Ids.
Because it is very difficult to read vertically, I am trying to create a view to show them all 750 fields in a single row as below.
Tkt_Id, Field_Name_1, Field_Name_2, Field_Name_3, Field_Name_4, Field_Name_5... Field_Name_750
1 001 002 003 004 005 ... 750
Plesae let me know it makes sense.
Thank you for your time and help!
TKT_DETAILS
TKT_Id
Field_Id
Field_Value
There are about 750 different fields numbered as 1 thru 750. Each row contains only 1 field. There are 750 rows for every single record vertically.
FIELD_DETAILS
Field_Id
Field_Name
This table contains Field Names of the every Field_Ids.
Because it is very difficult to read vertically, I am trying to create a view to show them all 750 fields in a single row as below.
Tkt_Id, Field_Name_1, Field_Name_2, Field_Name_3, Field_Name_4, Field_Name_5... Field_Name_750
1 001 002 003 004 005 ... 750
Plesae let me know it makes sense.
Thank you for your time and help!
Plesae let me know it makes sense.
No, it makes no sense. Cause someone has chosen the EAV model for some purpose. Otherwise you would have a table with sparse columns.
btw, there is the entity key missing in you table.
An dynamic pivot over 750 columns and god knows how many rows will give you pretty slow execution times.
Otherwise it is a simple dynamic SQL. So why didn't you tried that??
DECLARE @Columns NVARCHAR(MAX) = STUFF(
(
SELECT ', ' + QUOTE_NAME(Field_Name)
FROM FIELD_DETAILS
ORDER BY Field_Name
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 2, '');
DECLARE @Statement NVARCHAR(MAX) = N'
WITH Data AS
(
SELECT D.TKT_Id,
D.Field_Value,
FD.Field_Name
FROM TKT_DETAILS D
INNER JOIN FIELD_DETAILS FD ON FD.Field_ID = D.Field_ID
)
SELECT TKT_Id,
@Columns
FROM Data
PIVOT ( MAX(Field_Value) FOR Field_Name IN ( @Columns )) P;
';
SET @Statement = REPLACE(@Statement, '@Columns', @Columns);
EXECUTE (@Statement);
with (Field_Name) and (TKT_Id, Field_Id) being unique.
ASKER
PIVOT is extremely slow. That's why I am not using it.
The problem is your data model, I thought I was clear about that.. It does not matter, whether you use the PIVOT clause or you use a manual pivot.
Either you review your model or you just work with it.
Obviously in that model, the clustered index on TKT_DETAILS must have TKT_Id as first column.
Either you review your model or you just work with it.
Obviously in that model, the clustered index on TKT_DETAILS must have TKT_Id as first column.
ASKER
ste5an, I did not understand your reply. Is it possible for your to write the query?
Thank you!
Thank you!
I posted a query. You need to the check clustered index of that table. And when necessary and possible, then change it.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.