Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Pivoting the table Expand / Collapse
Author
Message
Posted Friday, August 10, 2012 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:22 PM
Points: 21, Visits: 166
I need some help in pivoting the table.
The table is like this:
File Main Sub Text
1 1 A. hello
1 1 B. SQL
5 1 A. central
5 1 B. .com

The modified table look like this

File Main A. B.
1 1 hello SQL
5 1 central .com

All text rows related to A come under column A and same B.

i tried a lot with different Pivoting queries but i'm getting either NULLS or empty fields.

Can anyone suggest me how to get this.

Thanks in Advance!
Post #1343554
Posted Friday, August 10, 2012 11:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:22 AM
Points: 1,912, Visits: 19,378
Hi

will you only "ever" have columns A and B to pivot on?


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1343557
Posted Friday, August 10, 2012 11:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223
If it fixed number of columns classic cross tab should work:


CREATE TABLE #Temp([File] INT, Main INT, Sub CHAR(2), [Text] VARCHAR(20));
INSERT INTO #Temp ([File], Main, Sub, [Text])
VALUES (1, 1, 'A.', 'hello'),
(1, 1, 'B.', 'SQL'),
(5, 1, 'A.', 'central'),
(5, 1, 'B.', '.com');

SELECT
[File]
,Main
,[A.] = MAX(CASE WHEN Sub = 'A.' THEN [Text] END)
,[B.] = MAX(CASE WHEN Sub = 'B.' THEN [Text] END)
FROM #Temp
GROUP BY [File], Main;

DROP TABLE #Temp;


Post #1343562
Posted Friday, August 10, 2012 11:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:22 PM
Points: 21, Visits: 166
Yes, I have only two group of values "A" and "B" need to be pivoted
Post #1343563
Posted Friday, August 10, 2012 11:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:22 AM
Points: 1,912, Visits: 19,378
ashwinrao.k23 (8/10/2012)
Yes, I have only two group of values "A" and "B" need to be pivoted


ok...in which case the solution from rVadim will work for you.

for more info, suggest you search under the "Authors" (left sidebar) for Jeff Moden and his articles on crosstabs and pivots



______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1343567
Posted Friday, August 10, 2012 12:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:22 PM
Points: 21, Visits: 166
thanks for the Reply!
If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..
Post #1343582
Posted Friday, August 10, 2012 12:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:22 AM
Points: 1,912, Visits: 19,378
ashwinrao.k23 (8/10/2012)
thanks for the Reply!
If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..


not quite sure what data fields are being "eaten"....maybe if you provide a script of the table....in the format that rVadim set out above, with a representative sample of data that demonstrates your problem (and expected results please)...then I am sure someone will be able to help



______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1343585
Posted Friday, August 10, 2012 12:12 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223
Exactly!

If sample you provided doesn't accurately reflect you actual situation then you need to provide a better sample.

People will be more willing to help if you include CREATE TABLE and INSERT statement to generate sample data.

Post #1343588
Posted Friday, August 10, 2012 12:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:22 AM
Points: 1,912, Visits: 19,378
ashwinrao.k23 (8/10/2012)
thanks for the Reply!
If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..


hmm...just a thought, but by any chance do you have data like this (based on your original description)

The table is like this:
File Main Sub Text
1 1 A. hello
1 1 B. SQL
5 1 A. central
5 1 B. .com
1 1 A. hello_again
1 1 B. SQL_again


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1343598
Posted Friday, August 10, 2012 1:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:22 PM
Points: 21, Visits: 166
No. all fields will not repeat as same. Text may be same but File and Main will be different..
Post #1343616
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse