Solved

How to convert data in table as column from a table appling self join or some other logic.

Posted on 2013-01-18
4
229 Views
Last Modified: 2013-01-18
I have the data in rows where in on the basis of those values i want to convert them into column.
The sample data and desired output is attached.
Sample-data.xlsx
0
Comment
Question by:searchsanjaysharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38795687
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 38795711
Hi,

I used the Union All to eliminate the difference between the delivered subject and the attended subject.

use ExpertsExchange
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.SomeData' )
		and type in( N'U' )
	)
	drop table dbo.SomeData
;
go

if not exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.SomeData' )
		and type in( N'U' )
	)
	create table dbo.SomeData (
		UID int
		, Subject varchar( 10 )
		, Attended int
		, Delivered int
	) on [PRIMARY]
go

insert dbo.SomeData( UID, Subject, Attended, Delivered )
	values( 101, 'SC-101', 	1,	4 )
	, ( 102, 	'SC-101',	2,	4 )
	, ( 103, 	'SC-101',	3,	4 )
	, ( 104, 	'SC-101',	4,	4 )
	, ( 105, 	'SC-101',	3,	6 )
	, ( 101, 	'SC-102',	4,	6 )
	, ( 102, 	'SC-102',	2,	6 )
	, ( 103, 	'SC-102',	6,	6 )
	, ( 104, 	'SC-102',	5,	6 )
	, ( 105, 	'SC-102',	4,	6 )
	, ( 101, 	'SC-103',	1,	3 )
	, ( 102, 	'SC-103',	1,	3 )
	, ( 103, 	'SC-103',	2,	3 )
	, ( 104, 	'SC-103',	3,	3 )
	, ( 105, 	'SC-103',	2,	3 )
	, ( 101, 	'SC-101',	1,	2 )
	, ( 102, 	'SC-101',	0,	2 )
	, ( 103, 	'SC-101',	1,	2 )
	, ( 104, 	'SC-101',	1,	2 )
	, ( 105, 	'SC-101',	1,	2 )
	, ( 101, 	'SC-102',	1,	3 )
	, ( 102, 	'SC-102',	2,	3 )
	, ( 103, 	'SC-102',	1,	3 )
	, ( 104, 	'SC-102',	2,	3 )
	, ( 105, 	'SC-102',	1,	3 )
	, ( 101, 	'SC-103',	0,	1 )
	, ( 102, 	'SC-103',	0,	1 )
	, ( 103, 	'SC-103',	1,	1 )
	, ( 104, 	'SC-103',	1,	1 )
	, ( 105,	'SC-103',	0,	1 )
;

select *
from dbo.SomeData
;

select
	UID
	, Subject + ' (Attended)' as Subject
	, Attended as Attendance
from dbo.SomeData

union all

select
	UID
	, Subject + ' (Delivered)' as Subject
	, Delivered as Attendance
from dbo.SomeData
;

select 
	UID
	, [SC-101 (Attended)]
	, [SC-101 (Delivered)]
	, [SC-102 (Attended)]
	, [SC-102 (Delivered)]
	, [SC-103 (Attended)]
	, [SC-103 (Delivered)]
from (
	select
		UID
		, Subject + ' (Attended)' as Subject
		, Attended as Attendance
	from dbo.SomeData

	union all

	select
		UID
		, Subject + ' (Delivered)' as Subject
		, Delivered as Attendance
	from dbo.SomeData
) p
pivot (
	sum( Attendance )
	for Subject in ( [SC-101 (Attended)], [SC-101 (Delivered)], [SC-102 (Attended)], [SC-102 (Delivered)], [SC-103 (Attended)], [SC-103 (Delivered)] )
	) as pvt
order by
	UID
;

Open in new window


HTH
  David

PS I have a database I keep as a testbed called ExpertsExchange (grin)
0
 

Author Comment

by:searchsanjaysharma
ID: 38795782
This i can achieve using group by also. i want to convert those into columns.
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 38795784
Tx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question