August 10, 2012 at 11:15 am
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!
August 10, 2012 at 11:27 am
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
August 10, 2012 at 11:38 am
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;
--Vadim R.
August 10, 2012 at 11:39 am
Yes, I have only two group of values "A" and "B" need to be pivoted
August 10, 2012 at 11:44 am
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
August 10, 2012 at 12:02 pm
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..
August 10, 2012 at 12:07 pm
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
August 10, 2012 at 12:12 pm
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.
--Vadim R.
August 10, 2012 at 12:31 pm
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
August 10, 2012 at 1:09 pm
No. all fields will not repeat as same. Text may be same but File and Main will be different..
August 10, 2012 at 1:12 pm
Combination of File + Main + Sub should be unique. Is that so?
--Vadim R.
August 10, 2012 at 1:24 pm
Here is the scenario please..
For exmaple if i have the fields like below
File Main Sub Text
917 2 A. Text1
917 2 B. Text2
917 2 A. Text3
917 2 B. Text4
917 2 A. Text5
917 2 B. Text6
If i use Aggregate function the result is only 1 field under A and B column which are maximum of available rows.
but i need to get all fields.
August 10, 2012 at 1:33 pm
ashwinrao.k23 (8/10/2012)
Here is the scenario please..For exmaple if i have the fields like below
File Main Sub Text
917 2 A. Text1
917 2 B. Text2
917 2 A. Text3
917 2 B. Text4
917 2 A. Text5
917 2 B. Text6
If i use Aggregate function the result is only 1 field under A and B column which are maximum of available rows.
but i need to get all fields.
ok...so pls provide the results you want based on the data you have provided
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 10, 2012 at 2:04 pm
i need something like.
File Main A B
917 2 Text1 Text2
917 2 Text3 Text4
917 2 Text5 Text6
August 10, 2012 at 2:58 pm
I can do it for given dataset but generally speaking how would you know which TextA should be paired with which TextB? I do it simply by including [Text] in the order by, which works for the given setup. Does it matter that Test1 goes with Text2? Can Text1 be paired with Text6, for example?
CREATE TABLE #Temp([File] INT, Main INT, Sub CHAR(2), [Text] VARCHAR(20));
INSERT INTO #Temp ([File], Main, Sub, [Text])
VALUES (917, 2, 'A.', 'Text1'),
(917, 2, 'B.', 'Text2'),
(917, 2, 'A.', 'Text3'),
(917, 2, 'B.', 'Text4'),
(917, 2, 'A.', 'Text5'),
(917, 2, 'B.', 'Text6')
--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
;WITH TempCTE AS (
SELECT
[File], Main, Sub, [Text]
,RowNum = ROW_NUMBER() OVER (PARTITION BY [File], Main, Sub ORDER BY [File], Main, Sub, [Text])
FROM #Temp
)
SELECT
[File]
,Main
,[A.] = MAX(CASE WHEN Sub = 'A.' THEN [Text] END)
,[B.] = MAX(CASE WHEN Sub = 'B.' THEN [Text] END)
FROM TempCTE
GROUP BY [File], Main, RowNum
DROP TABLE #Temp;
--Vadim R.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply