March 14, 2010 at 9:34 pm
Does anyone know how I can create Column2 data using SQL?
Column1 Column2
A 1
A 2
B 1
B 2
B 3
C 1
etc. ......
I'm not familiar using the loop function. Thanks!
March 15, 2010 at 12:04 am
No need for a loop.
-- Test table
CREATE TABLE #Sample
(
column1 CHAR(1) NOT NULL
);
GO
-- Sample data
INSERT #Sample (column1)
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'B' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C';
GO
-- Show the sample data
SELECT S.column1
FROM #Sample S
ORDER BY
S.column1 ASC;
GO
-- Method 1: Compute column 2
SELECT S.column1,
column2 =
ROW_NUMBER() OVER (
PARTITION BY S.column1
ORDER BY (S.column1))
FROM #Sample S
ORDER BY
column1 ASC;
GO
-- Method 2:
-- Add a column to the sample table
ALTER TABLE #Sample
ADD column2 BIGINT NULL;
GO
-- Update the new column with the number values
UPDATE SU
SET column2 = new_column2
FROM (
SELECT S.column1,
S.column2,
new_column2 =
ROW_NUMBER() OVER (
PARTITION BY S.column1
ORDER BY (S.column1))
FROM #Sample S
) SU
GO
-- Show the contents of the sample table
SELECT S.column1,
S.column2
FROM #Sample S
ORDER BY
S.column1 ASC;
GO
-- Clean up
DROP TABLE #Sample;
Paul
edit: added PARTITION BY clause
March 15, 2010 at 12:19 am
I guess the original poster needed a solution using the partition clause.
I have just made small change to pauls method1. It should work
Regards,
Raj
March 15, 2010 at 12:35 am
arr.nagaraj (3/15/2010)
I guess the original poster needed a solution using the partition clause.
You are right of course. I have updated my code sample to avoid confusion. Thank you.
March 15, 2010 at 12:45 am
Welcome 🙂
Regards,
Raj
March 15, 2010 at 11:39 am
This works!!! Thanks. I have another question:
Do you know how to pivot transform the data so that it looks like this:
Unpivoted Data:
Column1 Column2 Column3
A 1 Apple
A 2 Orange
B 1 Orange
B 2 Banana
B 3 Apple
C 1 Orange
Pivoted Result:
1 2 3
A Apple Orange Null
B Orange Banana Apple
C Orange Null Null
March 15, 2010 at 3:57 pm
redrabbit9999 (3/15/2010)
This works!!! Thanks. I have another question:Do you know how to pivot transform the data so that it looks like this:
Unpivoted Data:
Column1 Column2 Column3
A 1 Apple
A 2 Orange
B 1 Orange
B 2 Banana
B 3 Apple
C 1 Orange
Pivoted Result:
1 2 3
A Apple Orange Null
B Orange Banana Apple
C Orange Null Null
Please have a look at the DynamicCrossTab link in my signature. I guess you'll need a dynamic solution since the number of resulting columns may vary.
March 15, 2010 at 9:36 pm
-- Test table
CREATE TABLE #Sample
(
column1 CHAR(1) NOT NULL,
column2 INTEGER NOT NULL,
column3 VARCHAR(10) NOT NULL
);
GO
-- Sample data
INSERT #Sample (column1, column2, column3)
SELECT 'A', 1, 'Apple' UNION ALL
SELECT 'A', 2, 'Orange' UNION ALL
SELECT 'B', 1, 'Orange' UNION ALL
SELECT 'B', 2, 'Banana' UNION ALL
SELECT 'B', 3, 'Apple' UNION ALL
SELECT 'C', 1, 'Orange';
GO
-- Solution 1
SELECT P.column1,
P.[1],
P.[2],
P.[3]
FROM #Sample S
PIVOT (
MAX(column3) FOR
column2 IN ([1], [2], [3])
) P;
GO
-- Solution 2
SELECT S.column1,
[1] = MAX(CASE WHEN S.column2 = 1 THEN column3 ELSE NULL END),
[2] = MAX(CASE WHEN S.column2 = 2 THEN column3 ELSE NULL END),
[3] = MAX(CASE WHEN S.column2 = 3 THEN column3 ELSE NULL END)
FROM #Sample S
GROUP BY
S.column1;
GO
-- Tidy up
DROP TABLE #Sample;
Next time, please create the sample data yourself, thanks.
Paul
March 16, 2010 at 11:26 am
Thanks for all your help! I really appreciate it.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply