Link to home
Start Free TrialLog in
Avatar of RadhaKrishnaKiJaya
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)
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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?
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.
Avatar of RadhaKrishnaKiJaya
RadhaKrishnaKiJaya

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!
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);

Open in new window


with (Field_Name) and (TKT_Id, Field_Id) being unique.
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.
ste5an, I did not understand your reply.  Is it possible for your to write the query?

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 TRIAL
Members 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.