Sql Query Help - Urgent

[Bairagi From Bapatla]

How to transpose the data in SQL so that the row data becomes columns heading when joining tables.

 

For instance          

 

ID     Answer .........

1         Yes

2          No

3          Yes

.....

         

 

should change to.............

 

ID1   ID2      ID3.....

Yes   No     Yes

 

result bold lo unnatu ravali... query please...

[ChampakDas]

PIVOT

[Bairagi From Bapatla]

PIVOT

antee.... basic ga idhi naku kadu so naku aa terminology telidu... clear ga cheppu...

[150bryant]

select  id,

max(case when ID =1 then 'answer') as Id1,

 max(case when ID =2 then 'answer') as Id2,

 max(case when ID =3 then 'answer') as Id3

 

 

 from table 

 

group by id, answer

[Osama Bin Gandhi]

https://www.simple-talk.com/sql/t-sql-programming/switching-rows-and-columns-in-sql/

[Diana]

Q: key        sourcecolumnname  columnvalue

8308862 Name                     1a234
8308862 GrossTons              1a235
8308862 CallSign                  1a236
8308862 YearofBuild             1a237
8308862 Type                      1a238
8308862 Date                      1a239
8308862 NetTons                 1a245
8308862 OfficialNumber       1a247
8308862 PlaceofBuild           1a278

Is there any possibility to display the above data in the format given below using SQL:

Key           Name   GrossTons  CallSign YearofBuild     Type  Date   NetTons  OfficialNumber  PlaceofBuild  

8308862    1a234   1a235           1a236       1a237      1a238  1a239   1a245    1a247                  1a278

Ans:

DECLARE @t AS TABLE (key1 varchar(25), [Soucecolumn Name] varchar(25), ColumnValue varchar(25))
INSERT INTO @t VALUES ('8308862', 'Name', '1a234')
,('8308862', 'GrossTons', '1a235'),
('8308862', 'CallSign', '1a236'),
('8308862', 'YearofBuild','1a237'),
('8308862' ,'Type','1a238'),
('8308862','Date','1a239'),
('8308862', 'NetTons','1a245'),
('8308862','OfficialNumber','1a247'),
('8308862','PlaceofBuild','1a278'),
--For other key
('8308863', 'Name', '1a234')
,('8308863', 'GrossTons', '1a235'),
('8308863', 'CallSign', '1a236'),
('8308863', 'YearofBuild','1a237'),
('8308863' ,'Type','1a238'),
('8308863','Date','1a239'),
('8308863', 'NetTons','1a245'),
('8308863','OfficialNumber','1a247'),
('8308863','PlaceofBuild','1a278')

Query
SELECT key1, [Name], [GrossTons] ,[CallSign],[YearofBuild],
[Type], [Date],[NetTons],[OfficialNumber],[PlaceofBuild]
FROM @t
PIVOT (MAX(ColumnValue) FOR [Soucecolumn Name] IN ([Name], [GrossTons],[CallSign],[YearofBuild],
[Type], [Date],[NetTons],[OfficialNumber],[PlaceofBuild])) AS pvt

[Bairagi From Bapatla]

https://www.simple-talk.com/sql/t-sql-programming/switching-rows-and-columns-in-sql/

switching kadhu baa nenu adigindi... 

[Diana]

paina nenu paste chesindi useful ?

[dotnetrockz]

u need to use pivot..let me check my notes and see

[teliyadu_nuvvu]

i will use sys connect by path

 

 

refer the link

 

http://www.tek-tips.com/faqs.cfm?fid=7366

 

[SeemaLekka]

Ltt if still need help

[Diana]

or use this link

http://www.oracle.com/technetwork/issue-archive/2013/13-jul/o43sql-1958802.html

[Bairagi From Bapatla]

paina nenu paste chesindi useful ?

 

Chusthunam thnx for info...

u need to use pivot..let me check my notes and see

 

yeah emaina telisthe cheppu baa...

i will use sys connect by path

 

 

refer the link

 

http://www.tek-tips.com/faqs.cfm?fid=7366

idhi work avvala but thanks

 

Ltt if still need help

thanks

[dotnetrockz]

Q: key        sourcecolumnname  columnvalue

8308862 Name                     1a234
8308862 GrossTons              1a235
8308862 CallSign                  1a236
8308862 YearofBuild             1a237
8308862 Type                      1a238
8308862 Date                      1a239
8308862 NetTons                 1a245
8308862 OfficialNumber       1a247
8308862 PlaceofBuild           1a278

Is there any possibility to display the above data in the format given below using SQL:

Key           Name   GrossTons  CallSign YearofBuild     Type  Date   NetTons  OfficialNumber  PlaceofBuild  

8308862    1a234   1a235           1a236       1a237      1a238  1a239   1a245    1a247                  1a278

Ans:

DECLARE @t AS TABLE (key1 varchar(25), [Soucecolumn Name] varchar(25), ColumnValue varchar(25))
INSERT INTO @t VALUES ('8308862', 'Name', '1a234')
,('8308862', 'GrossTons', '1a235'),
('8308862', 'CallSign', '1a236'),
('8308862', 'YearofBuild','1a237'),
('8308862' ,'Type','1a238'),
('8308862','Date','1a239'),
('8308862', 'NetTons','1a245'),
('8308862','OfficialNumber','1a247'),
('8308862','PlaceofBuild','1a278'),
--For other key
('8308863', 'Name', '1a234')
,('8308863', 'GrossTons', '1a235'),
('8308863', 'CallSign', '1a236'),
('8308863', 'YearofBuild','1a237'),
('8308863' ,'Type','1a238'),
('8308863','Date','1a239'),
('8308863', 'NetTons','1a245'),
('8308863','OfficialNumber','1a247'),
('8308863','PlaceofBuild','1a278')

Query
SELECT key1, [Name], [GrossTons] ,[CallSign],[YearofBuild],
[Type], [Date],[NetTons],[OfficialNumber],[PlaceofBuild]
FROM @t
PIVOT (MAX(ColumnValue) FOR [Soucecolumn Name] IN ([Name], [GrossTons],[CallSign],[YearofBuild],
[Type], [Date],[NetTons],[OfficialNumber],[PlaceofBuild])) AS pvt

 

 

u need to use pivot..let me check my notes and see

[150bryant]

http://stackoverflow.com/questions/3392956/sql-how-to-transpose

 

http://stackoverflow.com/questions/14066927/what-are-some-good-ways-to-transpose-data-in-a-sql-table-from-row-columns-to-col