RadhaKrishnaKiJaya
asked on
Display SQL table rows horizontally
Hello Experts,
I am trying to display a table rows horizontally by TKT_Id. 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 how to do it without using PIVOT. Thank you in advance for your time and help!
I am trying to display a table rows horizontally by TKT_Id. 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 how to do it without using PIVOT. Thank you in advance for your time and help!
The linked article shows a few ways, 3 of which use PIVOT. Asker specifically stated without using PIVOT. I don't believe the other one be used in a view definition.
I'm thinking there is a better way to do this with some sort of windowed aggregate or XML trick, but it is escaping me at the moment. This is more the brute force method. Not extremely efficient, but it should work.
I'm thinking there is a better way to do this with some sort of windowed aggregate or XML trick, but it is escaping me at the moment. This is more the brute force method. Not extremely efficient, but it should work.
SELECT t.tkt_id,
Max(f1.field_value),
Max(f2.field_value),
Max(f3.field_value),
Max(f4.field_value),
Max(f5.field_value)
FROM tkt_details t
left outer join tkt_details f1
ON t.tkt_id = f1.tkt_id
AND f1.field_id = 1
left outer join tkt_details f2
ON t.tkt_id = f2.tkt_id
AND f2.field_id = 2
left outer join tkt_details f3
ON t.tkt_id = f3.tkt_id
AND f3.field_id = 3
left outer join tkt_details f4
ON t.tkt_id = f4.tkt_id
AND f4.field_id = 4
left outer join tkt_details f5
ON t.tkt_id = f5.tkt_id
AND f5.field_id = 5
GROUP BY t.tkt_id;
RadhaKrishnaKiJaya,
How are you envisioning the user viewing a "report" of 750 columns of data, that feels just as awkward as 750 rows of data, so wondering if perhaps you have something interesting in mind? Or are you exporting that to Excel, or some other reporting platform, etc? If comma delimited is an option there might be other approaches...
»bp
How are you envisioning the user viewing a "report" of 750 columns of data, that feels just as awkward as 750 rows of data, so wondering if perhaps you have something interesting in mind? Or are you exporting that to Excel, or some other reporting platform, etc? If comma delimited is an option there might be other approaches...
»bp
>> 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
>> Plesae let me know how to do it without using PIVOT
Ideally PIVOT is the good approach to achieve your requirement(as explained above by other experts as well) but why you don't want that..
Reading 750 rows horizontally from an application or report is also difficult and not user friendly as well..
Please share your requirements better so that we can suggest better approaches.
>> Plesae let me know how to do it without using PIVOT
Ideally PIVOT is the good approach to achieve your requirement(as explained above by other experts as well) but why you don't want that..
Reading 750 rows horizontally from an application or report is also difficult and not user friendly as well..
Please share your requirements better so that we can suggest better approaches.
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.
Multiple options to transposing rows into columns - SQL Shack - articles about database auditing, server performance, data recovery, and more
»bp