How to Pivot values stored as rows not columns

Question

How to Pivot values stored as rows not columns

RobTurton

Valued Member

Points: 65
More actions
September 17, 2020 at 7:16 am

#3789047

Hi all, I must say at the start that I can do basic SQL so I really don't know where to start on this one so come on this board for some expert help.
As context, all the Reference data is packed into 3 tables with each row holding only part of the structure. I have simplified the structure below to show how it all fits together.
SOURCE_SYSTEM_TABLE_A
id (PK)
tableName
SOURCE_SYSTEM_COLUMN_B
id(PK)
SOURCE_SYSTEM_TABLE_A_id
(FK)columnName
columnSequenceNumber
The above tables are filled with all the reference data names and columns e.g.
SOURCE_SYSTEM_TABLE_A
id tableName
1 refCountry
2 refContinents
SOURCE_SYSTEM_COLUMN_B
id SOURCE_SYSTEM_TABLE_A_id columnName columnSequenceNumber
100 1 id 1
101 1 countryName 2
102 1 countryCode 3
103 2 id 1
104 2 continentName 1
Now it gets really confusing, the 3rd table holds the actual values each of the columns:
COLUMN_VALUE_C
id(PK) SOURCE_SYSTEM_TABLE_A_id(fk) SOURCE_SYSTEM_COLUMN_B_id(fk) columnValue rowGroup(guid)
1000 1 100 826 AAAA
1001 1 101 United Kingdom AAAA
1002 1 102 GBR AAAA
1003 1 100 620 BBBB
1004 1 101 Portugal BBBB
1005 1 102 PRT BBBB
My problem is that I want to do a select across the 3 tables by selecting one of the reference table names in TABLE_A with the results being displayed in the normal way e.g.
refCountry
id countryName countryCode
826 United Kingdom GBR
620 Portugal PRT

Any pointers would be greatly appreciated. It's not the sort of table structure I have come across before.
Many thanks
Rob

September 18, 2020 at 12:10 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ktflash Default port Points: 1400 More actions · Answer 1

Not a solution since am at work, but for the future, if you have TSQL Questions, rather than what u did, its much more helpful and easier for you and the forum members if you post something like this. So everyone who wants to take a shot at your question doesnt have to write this by hand to test

create table #A
(A_ID int 
,tablename varchar(40)
)

create table #B
(B_ID int
,SOURCE_SYSTEM_TABLE_A_id varchar(40)
,columnName varchar(40)
,columnSequenceNumber int
)


create table #C
(C_ID int 
,A_ID int
,B_ID int
,columnValue varchar(40)
,rowGroup varchar(40)
)

insert into #A
values (1,'refCountry'),(2,'refContinents')

insert into #B
values 
(100,1,'ID',1)
,(101,1,'countryName',1)
,(102,1,'countryCode',1)
,(103,2,'ID',1)
,(104,2,'continentName',1)

insert into #C
values 
(1000,1,100,'826','AAAA')
,(1001,1,101,'United','AAAA')
,(1002,1,102,'GBR','AAAA')
,(1003,1,100,'620','AAAA')
,(1004,1,101,'Portugal','BBBB')
,(1005,1,102,'PRT','BBBB')

select *
from #A


select *
from #B


select *
from #C

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

RobTurton Valued Member Points: 65 More actions · Answer 2

Ha, ok I am sorry. I was trying to explain the best way I could. Many thanks for the advice, I will do it that way in future.

Mike

Thom A SSC Guru Points: 99133 More actions · Answer 3

Using the sample data from above (with a couple of correction), you can use a cross tab to achieve what you're after:

SELECT MAX(CASE B.ColumnName WHEN 'ID' THEN C.ColumnValue END) AS ID,
       MAX(CASE B.ColumnName WHEN 'CountryName' THEN C.ColumnValue END) AS CountryName,
       MAX(CASE B.ColumnName WHEN 'CountryCode' THEN C.ColumnValue END) AS CountryCode
FROM #C C
     JOIN #B B ON C.B_ID = B.B_ID
GROUP BY C.rowGroup;

Note, however, that the real problem here is your design; it's massively denormalised. You really need to fix that. The end result, above, should be how your data is stored, not how you currently have it.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

DesNorton SSC-Insane Points: 24401 More actions · Answer 4

The following SQL should get the results as requested in your post.

SELECT      ID          = MAX( CASE WHEN col.columnName = 'ID'          THEN val.columnValue END )
          , countryName = MAX( CASE WHEN col.columnName = 'countryName' THEN val.columnValue END )
          , countryCode = MAX( CASE WHEN col.columnName = 'countryCode' THEN val.columnValue END )
FROM        SOURCE_SYSTEM_TABLE_A  AS tab
INNER JOIN  SOURCE_SYSTEM_COLUMN_B AS col
        ON  tab.id = col.SOURCE_SYSTEM_TABLE_A_id
INNER JOIN  COLUMN_VALUE_C         AS val
        ON  tab.id = val.SOURCE_SYSTEM_TABLE_A_id
       AND  col.id = val.SOURCE_SYSTEM_TABLE_B_id
WHERE       tab.tablename = 'refCountry'
GROUP BY    val.rowGroup;

Now, I would suggest that you take the time to familiarise yourself with these 2 articles by Jeff Moden to see how to do it dynamically

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

How to post data/code on a forum to get the best help.

RobTurton Valued Member Points: 65 More actions · Answer 5

Many thanks for your help both,

I will have a go in an hour and see whether I can get it working.

One added bit of complexity (I don't think I explained too well). TableB holds the structure for each reference data set (the columns for each are different).

refCounty has:

id

CountryName

CountryCode

refContinents has:

id

ContinentName

I am also trying to figure out how to inject the structure from TableB somehow into the query so at runtime I only need to provide the tablename, refCountry, refContinents etc.

Would I need to do this as 2 stages (i.e. retrieve the column structure from TableB) then pass these into the query.

Sorry if there is a better way to explain this

DesNorton SSC-Insane Points: 24401 More actions · Answer 6

Neither your example data nor requirements mentioned continent. If you don't come right, then come back with sample data that represents both continent and country, and show us what you have tried. then we can show you how close you are.

How to post data/code on a forum to get the best help.

RobTurton Valued Member Points: 65 More actions · Answer 7

Thanks Des,

If you look at the very first post my example showed both Country and continents as reference data in TableA and TableB (here I was trying to show how the data is actually stored).

SOURCE_SYSTEM_TABLE_A

id tableName

1 refCountry

2 refContinents

SOURCE_SYSTEM_COLUMN_B

id SOURCE_SYSTEM_TABLE_A_id columnName columnSequenceNumber

100 1 id 1

101 1 countryName 2

102 1 countryCode 3

103 2 id 1

104 2 continentName 1

I also tried to explain my requirement:

"My problem is that I want to do a select across the 3 tables by selecting one of the reference table names in TABLE_A with the results being displayed in the normal way"

I will have a go and let you all know how I get on.