topic Re: Listing Values, Columns Axis in Developer

Listing Values, Columns Axis

ez1138 — Mon, 22 Jun 2020 18:01:31 GMT

Hi, I have a large number of columns in SQL Server (150) that have values. Let's say the values are green, blue, red. My goal is to display all of the columns with a value of green from top to bottom vs. a table that wants to add the columns left to right.

Is there a way to do this?

Re: Listing Values, Columns Axis

lbendlin — Mon, 22 Jun 2020 18:33:09 GMT

Columns don't have values. They have row numbers (or indexes) which then point to cells that have values.

To swap columns and rows you can use Pivot/unpivot transforms.

You would need to run a custom function that identifies the names of the columns containing cellse with the value "green".

Then you would do a SelectColumns against that list

Then you would do the pivoting thing to swap rows and columns.

Now the question is - how many rows does your table have? This might be fine for 20 rows, but will certainly not work for millions of rows.

Re: Listing Values, Columns Axis

ez1138 — Mon, 22 Jun 2020 18:35:24 GMT

Thanks for the reply. I have a few hundred columns and only a few hundred rows. I have no clue, yet, on how to do what you mentioned although it moves the ball forward and I will research it. Thanks!

Re: Listing Values, Columns Axis

ez1138 — Thu, 25 Jun 2020 12:13:56 GMT

For your proposed solution, do you have any direction to perform/create the function?

Re: Listing Values, Columns Axis

lbendlin — Thu, 25 Jun 2020 14:47:08 GMT

provide some sample data and the expected result

Re: Listing Values, Columns Axis

ez1138 — Mon, 29 Jun 2020 19:45:17 GMT

Hi, here's the same information. For my real data, I have hundreds title, description and "status" columns.

Audit_ID	Audit_Date	Company_ID	ABC123_Title	ABC123_Description	ABC123_Status	XYZ123_Title	XYZ123_Description	XYZ123_Status
1	1/1/20	1	ABC123_Title #1	ABC123_Description #1	Implemented	XYZ123_Title #1	XYZ123_Description #1	Not Implemented
2	2/1/20	2	ABC123_Title #2	ABC123_Description #2	Implemented	XYZ123_Title #2	XYZ123_Description #2	Not Implemented
3	3/1/20	3	ABC123_Title #3	ABC123_Description #3	Implemented	XYZ123_Title #3	XYZ123_Description #3	Not Implemented
4	4/1/20	4	ABC123_Title #4	ABC123_Description #4	Implemented	XYZ123_Title #4	XYZ123_Description #4	Not Implemented
5	5/1/20	5	ABC123_Title #5	ABC123_Description #5	Implemented	XYZ123_Title #5	XYZ123_Description #5	Not Implemented

Ideally I would like to have a report showing the following:

Company 1

Date Performed (1/1/20)

Implemented

ABC123_Title

Not Implemented

XYZ123_Title

Re: Listing Values, Columns Axis

ez1138 — Mon, 29 Jun 2020 19:47:38 GMT

Sorry, tables smashed together and I didn't see a spot to upload an excel file.

Re: Listing Values, Columns Axis

v-lionel-msft — Mon, 06 Jul 2020 09:26:45 GMT

Hi @ez1138 ,

I don't particularly understand what you want.

Do you want to convert Table 1 to get Table 2?

Table1

Table2

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.