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!

Bill Prew

Several options discussed here:

Multiple options to transposing rows into columns - SQL Shack - articles about database auditing, server performance, data recovery, and more

»bp

johnsone

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.

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;

Open in new window

Bill Prew

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

Raja Jegan R

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

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

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.