SQL Transpose - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Transpose

Author

Topic

ArpitNarula
Starting Member

16 Posts

Posted - 2011-06-30 : 09:21:12

Hi There,

I have a table in the following format.

System --ID --Master System --ID
A --1 --C --10
A --2 --C --20
A --3 --C --30
A --4 --C --10
A --5 --C --20
B --10 --C --10
B --20 --C --20
B --30 --C --30

You can very well assume that Master System ID are from System B only.

And required output is something like this.

C --A --B
10 --1 --10
20 --2 --20
30 --3 --30
10 --4 --10
20 --5 --20

I tried to do a Pivot but i think i m missing somethin there.
If possible could you please give me a SQL for this.

Thanks in Advance

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-30 : 18:33:37

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

There is no such thing as a magical, generic “id” – it has to identify something in particular.

Here is my guess at your job:

CREATE TABLE Foobar
(system_name CHAR(2) NOT NULL,
system_id INTEGER NOT NULL PRIMARY KEY,
master_system_name CHAR(2) NOT NULL
REFERENCES Foobar(system_id),
master_system_id CHAR(2) NOT NULL
CHECK (master_system_id >= 10)
REFERENCES Foobar(system_id));

>> You can very well assume that Master System ID are from System B only. <<

NO! We have to enforce it; it is a business rule.

INSERT INTO Foobar
VALUES ('A', 1', 'C', 10),
('A', 2, 'C', 20),
('A', 3, 'C', 30),
('A', 4, 'C', 10),
('A', 5, 'C', 20),
('B', 10, 'C', 10),
('B', 20, 'C', 20),
('B', 30, 'C', 30);

I do not understand what you want. Want to try again?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

ArpitNarula
Starting Member

16 Posts

Posted - 2011-06-30 : 23:18:22

Sorry for not being clear enough.

So here is the deal.
There are 3 systems A, B and C.
And the ID's of system A and B are mapped to each other via C.

So the below table signifies that ID 1 of System A is equal to ID 10 of System C, And ID 10 Of System B is also equal to ID 10 of system C which eventually leads to conclusion that ID 1 of system A is equal to ID 10 of System B.
System --ID --Master System --ID
A --1 --C --10
A --2 --C --20
A --3 --C --30
A --4 --C --10
A --5 --C --20
B --10 --C --10
B --20 --C --20
B --30 --C --30

That is what I am trying to get from the required output where this mapping is more clearly evident.

C --A --B
10 --1 --10
20 --2 --20
30 --3 --30
10 --4 --10
20 --5 --20

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-01 : 13:48:39

Here is one way:

SELECT 
    AC.master_system_id AS C,
    AC.system_id AS A,
    B.system_id AS B
FROM 
    @Foobar AS AC
INNER JOIN
    @Foobar AS B
    ON AC.master_system_id = B.system_id
WHERE    
    B.system_name = 'B'
    AND AC.system_name = 'A'
    AND AC.master_system_name = 'C'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources